Search Multiple Rows

Dev Shed Forums Sponsor:

February 3rd, 2012, 07:24 PM

CarlosinFL

Contributing User

Join Date: Aug 2011

Posts: 51

Time spent in forums: 10 h 8 m 48 sec
Reputation Power: 2

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%';

February 3rd, 2012, 08:18 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 49 sec
Reputation Power: 4140

Code:

SELECT * 
  FROM users
 WHERE LOWER(fname) LIKE 'rich%'
    OR LOWER(lname) LIKE 'rich%'

which database system is this?

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

February 4th, 2012, 02:20 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,685

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 53 m 20 sec
Reputation Power: 284

Quote:

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

February 9th, 2012, 09:52 AM

Cameron0960

Contributing User

Join Date: Feb 2012

Posts: 37

Time spent in forums: 7 h 55 m 20 sec
Reputation Power: 2

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!

February 9th, 2012, 11:13 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 49 sec
Reputation Power: 4140

Quote:

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

February 9th, 2012, 11:14 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 49 sec
Reputation Power: 4140

Quote:

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!

February 9th, 2012, 01:10 PM

CarlosinFL

Contributing User

Join Date: Aug 2011

Posts: 51

Time spent in forums: 10 h 8 m 48 sec
Reputation Power: 2

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)

February 9th, 2012, 01:45 PM

Cameron0960

Contributing User

Join Date: Feb 2012

Posts: 37

Time spent in forums: 7 h 55 m 20 sec
Reputation Power: 2

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

February 9th, 2012, 02:04 PM

CarlosinFL

Contributing User

Join Date: Aug 2011

Posts: 51

Time spent in forums: 10 h 8 m 48 sec
Reputation Power: 2

Quote:

Originally Posted by Cameron0960

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

Thank you!

Search Multiple Rows

Developer Shed Advertisers and Affiliates