Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["        <li><a href=\"http://area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"http://careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2014 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \r\n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2014.7.30.1736\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </nos"], "copyright__copyright__copyright": ["ot help. If you look for beggining of word, it will be fast.\n\nSo, if you want to find some substring of the word \"substring\":\n\n1) good (fast) query will be \"sub*\", \"subs*\", etc. => will use the index\n\n2) Really bad query will be: \"*string\", \"str\", etc. => will not use the index\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}