Text Search - 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)

Text Search

Author

Topic

nickstation
Starting Member

4 Posts

Posted - 2008-12-10 : 13:16:36

I have a comma delimited string of words to search a VARCHAR(MAX) field for. The field is a description field.

How can I do this without creating SQL dynamically to execute?

I have tried many things, and feel it's easier than I'm making it.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-10 : 14:12:52

So you have a comma delimited search string, and you want to match these string in a Description field in VARCHAR(MAX) data type.
Does the order of words matter? Do you have to match all the words or any of these words? Please illustrate.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-11 : 02:10:23

Search for Array+SQL Server in google

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 02:43:22

quote:
Originally posted by nickstation

I have a comma delimited string of words to search a VARCHAR(MAX) field for. The field is a description field.

How can I do this without creating SQL dynamically to execute?

I have tried many things, and feel it's easier than I'm making it.

SELECT * FROM YourTable
WHERE ',' + @YourCSVlist + ',' LIKE  '%,' + description + ',%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 02:50:06

SELECT t.*
FROM YourTable t
INNER JOIN dbo.ParseValues(@CSVlist,',')f
ON f.Val=t.Description

ParseValues can be found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115367

nickstation
Starting Member

4 Posts

Posted - 2008-12-11 : 07:06:53

Thanks everyone for the replies,

visakh16, the ParseValues is a great function! Should be exactly what I need!

nickstation
Starting Member

4 Posts

Posted - 2008-12-11 : 08:20:04

I'm close now,


SELECT t.*
FROM MyTable t
INNER JOIN dbo.ParseValues('large, big, huge', ',') f
ON f.Val = t.Description

doesn't quite work, because now it's looking for direct matches of those words. I need to find if t.Description contains f.Val


SELECT t.*
FROM MyTable t
INNER JOIN dbo.ParseValues('large, big, huge', ',') f
ON t.Description LIKE '%' + f.Val + '%'

This is getting closer, but still only uses the first value returned from the ParseValues table

Let me also say I have the table Full Text Indexed, but in the end I need to be able to have the search criteria another table field. Something like this:


SELECT t.* FROM mySearchableTable t
JOIN mySearchCriteria s
ON (s.search1 = t.searchable1 OR s.search1 = '')
AND (s.search2 = t.searchable2 OR s.search2 = '')
WHERE FREETEXT(t.searchableDescriptionField, s.csvSearchCriteria)

I don't think that works, but hopefully you'll see what I'm trying to do here...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 10:18:21

[code]SELECT t.*
FROM MyTable t
INNER JOIN dbo.ParseValues('large, big, huge', ',') f
ON '%'+t.Description+'%' LIKE '%' + f.Val + '%'
[/code]

nickstation
Starting Member

4 Posts

Posted - 2008-12-11 : 10:47:08

It still only searches on the first value returned by the ParseValues table. I almost need a

SELECT t.*
FROM MyTable t
INNER JOIN dbo.ParseValues('large, big, huge', ',') f
ON t.Description IN ('%' + f.Val + '%')

Is there a way to use IN and LIKE together?

It sounds like valley girl SQL:
"WHERE t.Description LIKE IN (f.Val)"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources