1

I have some table 'TableName' like Id,Name:

1 | something
2 | _something
3 | something like that
4 | some_thing
5 | ...

I want to get all rows from this table where name containes 'some'. I have 2 ways:

SELECT * FROM TableName WHERE Name like '%some%'

Result is table :

1 | something
2 | _something
3 | something like that
4 | some_thing

But if I use CONTAINS function 

SELECT * FROM TableName WHERE CONTAINS(Name,'"*some*"')

I get only 

1 | something
3 | something like that

What should I do to make CONTAINS function work properly?

Kate
  • 751
  • 1
  • 12
  • 26

3 Answers3

2

The last time I looked (admittedly SQL Server 2000) CONTAINS didn't support wildcard matching at the beginning of words, only at the end. Also, you might need to check your noise files to see if the "_" character is being ignored.

Also see

How do you get leading wildcard full-text searches to work in SQL Server?

Community
  • 1
  • 1
davek
  • 22,499
  • 9
  • 75
  • 95
0

http://doc.ddart.net/mssql/sql70/ca-co_15.htm

If you read this article you will see that * means prefix this means that word must start with this, but like means the word contains key phrase.

Best Regards, Iordan

Bridge
  • 29,818
  • 9
  • 60
  • 82
IordanTanev
  • 6,130
  • 5
  • 40
  • 49
  • I know what prefix * stands for, I use it to get strings like 'something like that' or 'this is something'. I'm interesting in how to get string with underlinings using CONTAINS function. – Kate Jan 13 '10 at 10:00
  • 1
    then you must add one more CONTAINS function with parent with underline SELECT * FROM TableName WHERE CONTAINS(Name,'"*some*"') OR CONTAINS(Name,'"*_some*"') – IordanTanev Jan 13 '10 at 10:22
  • If I use it I can get strings like 'something', '_something', but can't get 'some_thing'. I want get all string with word 'some', so you way is not panacea. – Kate Jan 13 '10 at 10:33
  • 1
    the do it with like operatot or just add OR CONTAINS(Name,'"*some_*"') to previous expression i prefer using like – IordanTanev Jan 13 '10 at 11:00
  • Thank you, I try it but CONTAINS(Name,'"*_some*"') don't match 'this_something' string, only '_something'. On the one hand CONTAINS work faster then Like statement, on the other CONTAINS doesn't match string I wish, so you are right I better use Like. – Kate Jan 13 '10 at 11:49
  • I read some where on the internet that if the column you filter with like has index on it and the like filter has % only on right then the index works and like is faster. CONTAINS(Name,'"_some"') OR like 'some%' might be faster then just like '%some%' but you have to test this. – IordanTanev Jan 13 '10 at 12:52
  • Thank you answer, but I need '%some%' statement. – Kate Jan 13 '10 at 14:28
0

Try this:

SELECT * FROM TableName WHERE CONTAINS(Name,'some')
codaddict
  • 445,704
  • 82
  • 492
  • 529