tsql - Escaping an ampersand in SQL Server Full-Text Search query using CONTAINSTABLE -

i have peculiar case. asp.net page calls stored procedure of ours performs full-text search query on our database. of commonly searched strings include ampersand because few brands of our products (well-known brands, too) have & in name.

it turns out in case no results unless escape ampersand (\&), , in other case no results only if escape ampersand.

i don't know if relevant, (without giving out brand names) 1 ends in &b , other 1 in &c.

is possible these strings (&b or &c) have special meaning of own? , escaping them i'm passing special string t-sql?

edit

additional info: after further testing, proved error in stored procedure itself. calling & or \& yields different results.

i'll try post selected parts of stored procedures. won't post all, because of isn't relevant.

the vparambuca parameter 1 causes troubles. values 'word&letter' or word\&letter.

set @ricercaa = '''formsof(inflectional,"' +     replace(ltrim(rtrim(@vparambuca)),' ', '") , formsof(inflectional,"') + '")''' 

the variable @ricercaa used create query string:

[...] products ft_tbl left outer join containstable (products, sign1, '+ @ricercaa + ') colsign1_0 on ft_tbl.id = colsign1_0.[key] left outer join containstable (products, manufactureradditionaltext, '+ @ricercaa + ') colmanufactureradditionaltext_0 on ft_tbl.id = colmanufactureradditionaltext_0.[key] left outer join containstable (products, manufacturerforsearch, '+ @ricercaa + ') colmanufacturer_0 on ft_tbl.id = colmanufacturer_0.[key] left outer join containstable (products, tuttalariga, '+ @ricercaa + ') coltuttalariga_0 on ft_tbl.id = coltuttalariga_0.[key] [...] 

edit 2

many @srutzky pointing me in right direction! in meanwhile, found data inconsistency 1 of brands & in name modified not have &, , other 1 wasn't modified (bottom line, current problem caused that: partial fix made in past).

anyway, on track. understand & character in containstable function treated logical , (non bitwise).

i still need solution that. this answer gives solution doesn't work me (the conditions not same mine). how perform containstable search string ampersand in it? preferably without having transform ampersand safe character?

the odd behavior seeing due contains , containstable functions (both used sql server's full text search feature) using ampersand ( & ) character equivalent and operator. following statement taken documentation contains:

the ampersand symbol (&) may used instead of , keyword represent , operator.

there no mention of there being escape character (and back-slash isn't typically escape character in sql anyway).

---

update

based on information provided in "edit 2" of question, , additional research, not need escape anything. seems putting search phrases in double-quotes (as result of using formsof) treats & either literal or word-breaker, depending on values on both sides of &. try following examples:

declare @term nvarchar(100);  set @term = n'bob&sally'; -- 48 rows --set @term = n'bob\&sally'; -- 48 rows --set @term = n'r&f'; -- 4 rows --set @term = n'r\&f'; -- 24 rows  set @term = n'formsof(inflectional,"' + @term + '")';  select * sys.dm_fts_parser(@term, 1033, 0, 0); select * sys.dm_fts_parser(@term, 1033, 0, 1); select * sys.dm_fts_parser(@term, 1033, null, 0); select * sys.dm_fts_parser(@term, 1033, null, 1); 

the results bob&sally , bob\&sally same, , in both cases bob , sally separated , never combined single exact-match string.

the results between r&f , r\&f, however, not same. r&f ever treated single, exact-match string because r , f alone not known words. on other hand, adding in back-slash separates 2 letter since \ word-breaker, in case both r , f.

given stated in update have "data inconsistency, 1 of brands "&" in name modified not have "&", , other 1 wasn't", suspect when not add in \ character brand not modified (since exact match full term). when do add in \ character, brand was modified have & removed, since searching on both pieces, each 1 matching part of brand name.

i fix data consistent: update brand names had & removed put ampersands in. when people search using & without \ added, exact match. behavior consisted across data, , not require adding code circumvent natural operation of fts, seems error-prone approach.