Queyr on column with multiple words

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

Queyr on column with multiple words

Author

Topic

omar123
Starting Member

3 Posts

Posted - 2006-04-02 : 11:06:30

I have a column that has multiple word delimited by a space or comma such as:

crime horror love

I want to perform a query so that a search for one of these words returns the record. THis is what I have so far:

SELECT columnname
FROM tablename
WHERE columnname LIKE 'strSearchVar'

This only returns records with one word in the column that matches. it ignores columns with multiple words.

thanks!

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-02 : 11:25:08

SELECT columnname
FROM tablename
WHERE columnname LIKE '%strSearchVar%'

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-02 : 11:30:24

WHERE ',' + replace(columnname,' ', ',') + ',' LIKE '%,strSearchVar,%'

or with a variable
WHERE ',' + replace(columnname,' ', ',') + ',' LIKE '%,' + @strSearchVar+ ',%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

omar123
Starting Member

3 Posts

Posted - 2006-04-02 : 12:10:15

quote:
Originally posted by chiragkhabaria

SELECT columnname
FROM tablename
WHERE columnname LIKE '%strSearchVar%'

Thanks that works. The only thing is it returns many records if someone puts in the first few letters of a word instead of the whole word, which can be a problem at times.

quote:
WHERE ',' + replace(columnname,' ', ',') + ',' LIKE '%,strSearchVar,%'

or with a variable
WHERE ',' + replace(columnname,' ', ',') + ',' LIKE '%,' + @strSearchVar+ ',%'

I want to test this too but I'm getting errors with the replace function. Is this compatible with Access too.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-02 : 17:36:22

There is an access forum here - this is sql server.
For access it should be " not ' and & not +.
Not sure about replace - have a look at the functions available.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

omar123
Starting Member

3 Posts

Posted - 2006-04-02 : 18:11:32

I don't think replace works with access, but thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-02 : 21:19:25

Yes it does.
try
WHERE "," & replace(columnname," ", ",") & "," LIKE "*,strSearchVar,*"

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources