CONTAINS with Multiple Values - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

CONTAINS with Multiple Values

Author

Topic

rekiller
Starting Member

31 Posts

Posted - 2007-11-19 : 12:39:08

I use Contains.


select * from cat_tables
where contains(description_table,'computer') 
UNION
select * from cat_tables
where contains(description_table,'mouse')

These code works cause i only need to search for both computer and mouse.
But i want to do something automatic like, having a Parameters Table

ID ParameterValue
1 Computer
2 Mouse
3 Keyboard
4 somethingElse

And do something like the following algorithm


select * from cat_tables
where contains(description_table,(SELECT ParameterValue FROM Parameters_Table))

Can you do something like that?

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-19 : 14:36:22

no, but here are some options that come to mind
1. you can perform a loop over the parameters table and store the results in a table variable
2. you can use dynamic sql to create the search condition arguement
3. you can use LIKE instead of CONTAINS to do what you want...
select distinct ? from cat_tables join parameters_table on cat_tables.description_table like '%' + parameters_table.parametervalue + '%'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-19 : 15:23:50

This should do what you want:


select * from cat_tables
where contains(description_table,'"computer" or "mouse" or "Keyboard" or "somethingElse"')

All this is documented fairly well in SQL Server Books Online

CODO ERGO SUM

rekiller
Starting Member

31 Posts

Posted - 2007-11-19 : 15:57:10

When i Said SomethingElse is that i can have from 1 to N more parameters.

I think solution is to construct the SQL, but i though it could be done with one simple Sql Clause

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-19 : 18:19:51

quote:
Originally posted by rekiller

When i Said SomethingElse is that i can have from 1 to N more parameters.

I think solution is to construct the SQL, but i though it could be done with one simple Sql Clause

Or you could just read SQL Server Books Online to see the easy way to do it.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources