1

I am fairly new to using ms SQL server and was wondering if there was a way to convert an integer column value into a string for use in a fulltext contains query.

Example:

Select 
   *, 
   (select count(t2.id) from table2 t2 where contains(t2.text, t.someinteger)) as number 
from table1 t`

Since t.someinteger is not a string, the query fails.

I have tried casting and converting the integer to a varchar but it is syntactically incorrect. Does anybody know how to achieve this?

Any help much appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Adam Jones
  • 11
  • 1
  • 3
  • It would help if you give the exact error you're getting, but the syntax is wrong anyway: the search term for `CONTAINS` is a single value, not multiple values from a column. So you can either declare a variable for the search term or use a constant, but the syntax you have isn't supported. – Pondlife Mar 25 '13 at 15:55
  • I would be careful, it could be possible that, based upon your code, you could become victim of SQL Injection – Dave Mar 25 '13 at 16:01
  • Are you trying to match the integer as a substring ("123" matches "0123" and "12345")? – Keith Mar 25 '13 at 16:10

1 Answers1

1

Full text contains will not work as you are expecting. Full text queries have many tricks. Maybe you should look at like operator:

select 1 where '1234' like '%' + cast(34 as nvarchar) + '%';

select 1 where '1234' like '%' + cast(35 as nvarchar) + '%';

Full text search is not for searching substrings as showed in this post:

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

How full text search works? Making it simple: it gets your text, splits it in tokens/words (think of it as tags), and indexes every tag/word.

So, if you look for a word/tag, it will be extremely fast. If you look for a substring in a word, it will behave just like a "LIKE" query.

From my own experience: if you look from substring of a word at end or middle, full text search will not help. If you look for beggining of word, it will be fast.

So, if you want to find some substring of the word "substring":

1) good (fast) query will be "sub*", "subs*", etc. => will use the index

2) Really bad query will be: "*string", "str", etc. => will not use the index

Community
  • 1
  • 1
jagra
  • 543
  • 2
  • 8
  • This is similar to what I currently have at the moment as a solution. I am looking into the full text match as it would execute faster over the 60000+ row data sets I need to migrate. – Adam Jones Mar 25 '13 at 16:00