Select rows that contains a small part in a search string with SQL

Question

I'm trying to run a strored procedure that selects rows (from a fulltext indexed column - sql-azure v12) where words contain a small part of a search word. For instance if a user types "natio" in the search string I want to return all the rows that contain "international". I red that I should use the prefix * before and after the search string but that doesn't work. Here is what I've tried so far.

SET @searchString = '*' + @searchString +'*'
or SET @searchString = @searchString +'*'
or SET @searchString = '*' + @searchString

SELECT * FROM myTable where FREETEXT(sWords, @searchString )
or
SELECT * FROM myTable where CONTAINS(sWords, @searchString )

None of the above combinations seem to work. It would only works if I typed the whole word (international in this case). Any help would be well appreciated.

EDIT

I got half an answer from the question link listed above:

SELECT *
FROM product
WHERE CONTAINS(name,'"*1340*"');

I used the same logic for my variable which became:

SET @searchString ='"*' + @searchString + '*"';

It solved half of what I want to accomplish. Now if I entered "inter" in the search string I would get all words that start with inter such as international. However if I entered "national", then I won't get "international".

I think I still need to dig deeper to find a complete solution...

See this : http://stackoverflow.com/questions/17102457/use-of-contains-in-sql-server — Taher Rahgooy, Aug 18 '15 at 07:07
See this as well: http://stackoverflow.com/questions/588025/sql-problem-using-contains-doesnt-work-but-like-works-fine — Taher Rahgooy, Aug 18 '15 at 09:04

score 1 · Answer 1 · edited May 23 '17 at 11:58

1

Unfortunately Full-Text Search only supports prefix wildcards: CONTAINS(<>, '"book*"') and this is same as Sql Server on premise behavior. You can find some suggestions on how to workaround this limitation here: Full text catalog/index search for %book% .

edited May 23 '17 at 11:58

Community

1
1

answered Aug 19 '15 at 17:39

MihaelaBlendea

243
1
4

score 0 · Answer 2 · answered Aug 18 '15 at 07:20

0

You can try with LIKE

SELECT * FROM myTable where sWords like '%'+@searchString+'%';

answered Aug 18 '15 at 07:20

Arun Palanisamy

5,281
6
28
53

I have to solve the problem with CONTAINS... LIKE will slower the performance – Gloria Aug 18 '15 at 08:29

Select rows that contains a small part in a search string with SQL

2 Answers2