CODEWITHSUNDEEP
sql-servert-sqlcontains
Tim Maxey
Tim Maxey

Reputation: 749

SQL Server Differences? With Contains

I can run this (its a function generated query string from an online advanced search where user can add address, city, zip among other things...):

SELECT * 
FROM MYTABLE
WHERE
    FREETEXT(pCity, 'Daytona Beach') 
    AND CONTAINS(pAddr, '"*9*" OR FORMSOF(thesaurus, "9")' )
    AND CONTAINS(pZip, '"*32118*" OR FORMSOF(thesaurus, "32118")')   
    AND CONTAINS(pAddr, '"*S*" OR FORMSOF(thesaurus, "S")')
    AND CONTAINS(pAddr, '"*Wild*" OR FORMSOF(thesaurus, "Wild")')   
    AND CONTAINS(pAddr, '"*Olive*" OR FORMSOF(thesaurus, "Olive")')
    AND CONTAINS(pAddr, '"*Ave*" OR FORMSOF(thesaurus, "Ave")')

And get the result I'm looking for, only one record: "9 S Wild Olive Ave Daytona Beach Fl 32118"

I copied the database over to SQL Server 2019, copied the thesaurus and ran EXEC sys.sp_fulltext_load_thesaurus_file 1033; etc, doubled checked to make sure all indexes where there etc and when I run the same query nothing returns, no errors, just can't find it.

If I run:

SELECT * 
FROM MYTABLE 
WHERE
    CONTAINS(pAddr, '"9 S Wild Olive Ave"' )

it returns 111 records with this address in there. So the data record is in the table...

QUESTION: old server is SQL Server 2014, new one is SQL Server 2019, is there something that changed with contains that is breaking this?

Thanks in advance, just wondering if someone else has ran into something like this or some setting I'm not bringing over that SQL Server 2014 likes and SQL Server 2019 doesn't.

Upvotes: 0

Views: 104

Answers (1)

Tim Maxey
Tim Maxey

Reputation: 749

So this has been fixed thanks to another user's question here full-text catalog index CONTAINS() doesn't work with numbers

Basically on the new server had to click on the properties of the fulltext and toggle the stoplist to Off. Re indexed and now same query works from old server...

Upvotes: 1

Related Questions