Search using LIKE - 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 2000 Forums

Transact-SQL (2000)

Search using LIKE

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2006-05-03 : 00:07:41

Hello,

I have to find a string within a field. The field contains many words. But the string must be an actual word, and not part of a word. So I did something like this:

SELECT * FROM Table1
WHERE field1 LIKE '% ' + @word1
OR field1 LIKE @word1 + ' %'
OR field1 LIKE '% ' + @word2 + ' %'

This will pick up ' word ' or 'word ' or ' word'. So far this works great. But I was just wondering if there is a more elegant way to do this with t-sql.

Thanks

Kristen
Test

22859 Posts

Posted - 2006-05-03 : 02:04:30

Whichever way its going to be a bit slow - unless you "split" field1 into the individual words and store them in an associated "words" table.

This would probably do the trick, possibly a bit better than what you have

SELECT *
FROM Table1
WHERE ' ' + field1 + ' ' LIKE '%[^A-Za-z0-9]' + @word1 + '[^A-Za-z0-9]%'

by pre-surrounding field1 with spaces you don't need to do separate StartsWith, Contains and EndsWith tests - you can just do the Contains. Instead of matching @word1 with a word that is delimited by space I've used "[^A-Za-z0-9]" instead - which will match any character which is NOT AlphaNumeric.

Surrounding field1 with spaces like this will mean that no indexes will be used for the test, so it will be a table scan every time, which will be slow if you have more than a few hundred, or possible thousand, rows to test.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-03 : 02:41:46

or

' '+ field1 + ' ' LIKE '% ' + @word1 + ' %'

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-03 : 04:36:20

How about this ?


Declare @tbl1 Table 
(
Word varchar(800)
)

Insert @tbl1 
Select @Word1 Union All 
Select @Word1 + ' ' Union All 
Select @Word2 

Select * From Table1 
Inner Join @tbl1 On Table1.Field1 Like '%' + @Tbl1.Word  + '%'

I dont know about performance

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

dcarva
Posting Yak Master

140 Posts

Posted - 2006-05-03 : 11:33:41

Thanks! I'll give those a shot.

dcarva
Posting Yak Master

140 Posts

Posted - 2006-05-04 : 10:24:36

All,

Here is what I decided to do. Basically, I am searching against 3 fields. I created a field called "Keywords". I ran a quick query that took all 3 fields and concatenated them into the keywords field, with leading and trailing spaces. I changed my query to:

SELECT * FROM Table1
WHERE Keywords LIKE '% ' + @word1 + ' %'

It's smokin' fast now, and it returns the correct results.

Thanks
Danny

Kristen
Test

22859 Posts

Posted - 2006-05-04 : 13:46:27

It won't find @word1 when its next to a punctuation symbol though - maybe that can't happen in your data?

SELECT 'OK' WHERE ' foo bar ' LIKE '% ' + 'foo' + ' %'
SELECT 'Fails' WHERE ' foo; bar ' LIKE '% ' + 'foo' + ' %'

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources