searching for multple values in a query....or passing a list/array to a query

[sj1187534]

Hi...I have a search engine that includes "search by location" functionality. The problem is when the user selects more than one from the listbox of locations, I have no way of searching those selected values in the database. Thats because, a single user in the database table has his desired locations as a string of locations from the locations listbox....

 

ex...in the database table, let's the user has his desired locations as Dallas, NewYork, Boston.

 

Now, in the search engine , if the search in locations are selected as Dallas and Boston (passes to the SQl server as "Dallas,Boston"), how do I pass this in the SQL query???

 

I cant use the "charindex" as it can only check the whole "dallas,boston" string and not a part of it......

 

I cant use the "like" keyword also because the order of the words matters here...

 

 

I hope you understand what I am talking about. Any help would be really appreciated...

 

Thanks,

~SJ

[Nerseus]

It sounds like you want to pass in a comma separated string (one string) to a proc and have it treat that string as individual strings, as if you had:

-- You WANT this to work
SELECT * FROM Table1 WHERE Location IN (@NameString)
-- But this is what it sees:
SELECT * FROM Table1 WHERE Location IN ('Dallas, Boston')
-- NOT This:
SELECT * FROM Table1 WHERE Location IN ('Dallas', 'Boston')

 

Unfortunately, there's no way to do that, easily. You can use the EXEC function to use dynamic SQL:

EXEC ('SELECT * FROM Table1 WHERE Location IN (' + @NameString + ')')

 

BUT, this only works with integer columns, not strings. Also, it makes debugging harder because you won't get any "compile time" error checking, such as invalid columns names.

 

An alternative would be to use XML. You'd have to pass in your parameter "Dallas, Boston" as something like "<Root><Table1><Row>Dallas,Boston</Row></Table1></Root>". You can then use sp_xml_preparedocument and OPENXML to treat this XML as a table to which you could join.

 

If you need a sample, let me know what your table looks like and the exact data (is it really strings like "Dallas,Boston" or more like "34, 66"?)

 

-Nerseus

[sj1187534]

infact...it is strings like i told u....I really appreciate if you can show me a sample...

 

Thanks

SJ

[Nerseus]

If you need a sample, let me know what your table looks like...

 

-Ner

[sj1187534]

Sorry about that....

 

 

userid | locations

(int) (csv string)

 

ex: 2 Dallas, Boston, Chicago

 

Now, I have to get all the records that have any of the words "Dallas,NewYork" in their locations field.

 

SJ