#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    56
    Rep Power
    4

    Search Multiple Rows


    I have a table in my database for users shown below and it has over 100+ rows. I'm trying to find the proper syntax for searching a term against multiple fields. I tried the following but it didn't work obviously since I have no idea what I'm doing:

    Table

    Code:
      Table "public.users"
      Column  |         Type          |                        Modifiers
    ----------+-----------------------+----------------------------------------------------------
     users_id | integer               | not null default nextval('users_users_id_seq'::regclass)
     fname    | character varying(20) | not null
     lname    | character varying(40) | not null
     email    | character varying(50) | not null
     dob      | date                  |
     state    | character(2)          |
     position | character varying(50) |
    Indexes:
        "users_pkey" PRIMARY KEY, btree (users_id)
        "users_email_key" UNIQUE CONSTRAINT, btree (email)
    I'm trying to look for the word 'rich%' in both fname and lname as incorrectly demonstrated below:

    Code:
    SELECT * FROM users
    WHERE lower(fname, lname)
    LIKE 'rich%';
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Code:
    SELECT * 
      FROM users
     WHERE LOWER(fname) LIKE 'rich%'
        OR LOWER(lname) LIKE 'rich%'
    which database system is this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Originally Posted by r937
    Code:
    SELECT * 
      FROM users
     WHERE LOWER(fname) LIKE 'rich%'
        OR LOWER(lname) LIKE 'rich%'
    which database system is this?
    The output looks very much like Postgres
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    103
    Rep Power
    3
    The syntax will depend heavily on how your server treats SQL syntax. The following code should work, but may need to be modified depending on your server.

    This will return a table with all the rows containing an fName with "rich" in it and all the rows containing an lname with "rich" in it.
    Code:
    SELECT *
    FROM users
    WHERE fname LIKE "rich%" OR lname LIKE "rich%"
    i.e.
    fName | lName
    Dan | Rich
    Rich | Steve

    This will return a table with all the rows containing an fName and lName with "rich" in it.
    Code:
    SELECT *
    FROM users
    WHERE fname LIKE "rich%" AND lname LIKE "rich%"
    i.e.
    fName | lName
    Rich | Rich
    DanRich | SteveRich

    Comments on this post

    • CarlosinFL agrees
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Cameron0960
    The syntax will depend heavily on how your server treats SQL syntax.
    indeed

    and your suggestions make no allowance for case sensitivity, do they

    refer to post #1 and the use of the LOWER() function

    note that the original poster has not yet answered my question about what database system this might be

    not all of them are case-insensitive

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Cameron0960
    This will return a table with all the rows containing an fName and lName with "rich" in it.
    no, not at all

    it will, however, return rows where "rich" as at the front of those columns

    Comments on this post

    • CarlosinFL agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    56
    Rep Power
    4
    I'm sorry I omitted my RDBMS which does happen to be PostgreSQL however I was hoping to get the answer based on just ANSI SQL which appears to be different depending on which system I'm using. The following did however work:

    Code:
    dps=# SELECT
      id,
      fname,
      lname,
      dept,
      manager
    FROM employees
    WHERE lower(fname) LIKE 'smith'
    OR lower(lname) LIKE 'smith'
    ORDER BY id;
     id | fname | lname  | dept | manager 
    ----+-------+--------+------+---------
     41 | Ryan  | Smith  |    2 |       2
     87 | David | Smith  |    2 |       2
     94 | Smith | Fields |    3 |       3
    (3 rows)
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    103
    Rep Power
    3
    Won't that only return rows that have an exact match to "Smith"? According to the people above, you would need to wildcard both sides of smith to get any names containing smith. i.e. "%smith%" That way you will retrieve names like BlackSmith as well.

    Thank you for correcting me above
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    56
    Rep Power
    4
    Originally Posted by Cameron0960
    Won't that only return rows that have an exact match to "Smith"? According to the people above, you would need to wildcard both sides of smith to get any names containing smith. i.e. "%smith%" That way you will retrieve names like BlackSmith as well.

    Thank you for correcting me above
    Yes. I forgot about the % wildcard but regardless the technique is understood for me so it's all good.

    Thank you!

IMN logo majestic logo threadwatch logo seochat tools logo