1

I want to get all the records from the one table which contain at least one word from the input string. For example, input parameter='Stack over flow':

select *
from sample
where  name like '%stack%'
    or name like '%over%'
    or name like '%flow%'

How do I search for records which contains 'stack' or 'over' or 'flow'?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Geeth
  • 5,282
  • 21
  • 82
  • 133
  • @codeka: i think the question is "What is the code?...How do do it?" – hallie Mar 25 '10 at 04:51
  • @hallie: I think I get it, he wants code that will transform the string "stack over flow" into an SQL query that will search for any name containing "stack" *or* "over" *or* "flow". The next question, then, is what language? – Dean Harding Mar 25 '10 at 04:54
  • I dont see why it is not working as is. – leppie Mar 25 '10 at 05:31
  • i have tried this http://stackoverflow.com/questions/2507330/sqlserver-split-operation sample to split the string. Its working. i dont know how to get this task – Geeth Mar 25 '10 at 05:38

1 Answers1

6

The code you gave should work. But don't use it. It won't be able to use any indexes, and so will likely be very slow. Look into a full text index and the CONTAINS keyword instead.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • Short of that, a regex could be used if CLR is enabled. – OMG Ponies Mar 25 '10 at 05:13
  • It sounds like there is only one input parameter that he needs parsed and then a like for each word. – Kelsey Mar 25 '10 at 05:15
  • 2
    Absolutely correct. Like with a wildcard as the first character is almost always a bad thing as it forces a table scan and full-text indexing will work much better. – HLGEM Mar 25 '10 at 14:21
  • I agree using leading wildcards with the LIKE is going to make this perform really badly, but if the table is small it won't matter that much. If it is a large table definitely full text index with contains. It appears he already has an answer from a different question on how to split the parameter into words. – GrowlingDog Mar 25 '10 at 14:24
  • @OMG - a Regular expression would still need to perform a table scan – Justin Mar 25 '10 at 15:28