search fields with multiple items for one item

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

search fields with multiple items for one item

Author

Topic

Samyra
Starting Member

2 Posts

Posted - 2010-08-27 : 12:00:14

I have one field that contains a list of all the states a person is interested in. for example (DC,DE,WA,TX,OR)

I need to return a list of all people interested in a particular state. Is there any way to do this?

Samyra Chrisp

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-27 : 12:01:59

WHERE state LIKE '%CA%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-28 : 01:39:30

it should be


WHERE ',' + state + ',' LIKE '%,CA,%'

else it will also bring cases where person is interested in similar states like KSA etc if that exists

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-28 : 13:06:37

if it's all U.S. states with 2 character codes, mine works. Also, need to be careful... '%,CA,%' won't return CA if it is first or last in the list

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-29 : 13:17:37

quote:
Originally posted by russell

if it's all U.S. states with 2 character codes, mine works. Also, need to be careful... '%,CA,%' won't return CA if it is first or last in the list

it will return notice the left part. its ',' + state + ','
not simply state

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-29 : 15:43:58

gotcha. i thought u meant just WHERE state LIKE '%,CA,%'

re-read. nice

Samyra
Starting Member

2 Posts

Posted - 2010-08-30 : 08:44:47

Thanks for your help. You got me going in the right direction.

the query ended up being

Select * from (query) as query where state
like '%' + @State + '%'

quote:
Originally posted by visakh16

it should be

WHERE ',' + state + ',' LIKE '%,CA,%'

else it will also bring cases where person is interested in similar states like KSA etc if that exists

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Samyra Chrisp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-30 : 09:50:01

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-30 : 09:51:29

quote:
Originally posted by russell

gotcha. i thought u meant just WHERE state LIKE '%,CA,%'

re-read. nice

No probs

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-08-30 : 19:25:00

>> ]I have one field [sic: columns are not fields] that contains a list of all the states a person is interested in. <<

Get a book on basic RDBMS, and read the section on First Normal Form (1NF). You are writing SQL as if it were 1950's COBOL.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources