Post reply

CONTAINS in full text index search

  • July 26, 2013 at 11:31 am

    #276367

    I've enabled full text index search on a column. When I query it WHERE contains (*, 'chicago and Boston' ) its giving results accurately but if I use WHERE contains (*, 'chicago and Boston and New York' ) its failing because there is a space in the word "New York". How can I make it to work?

    --Error message for WHERE contains (*, 'chicago and Boston and New York' )

    Msg 7630, Level 15, State 3, Line 2

    Syntax error near 'York' in the full-text search condition 'chicago and Boston and New York'.

    Thanks in advance

  • July 26, 2013 at 11:46 am

    #1636267

    See http://msdn.microsoft.com/en-us/library/ms187787.aspx

    It indicates:

    Examples of valid simple terms are "blue berry", blueberry, and "Microsoft SQL Server". Phrases should be enclosed in double quotation marks ("").

  • July 26, 2013 at 12:36 pm

    #1636284

    Thanks much! enclosing it in double quotes fixed the issue

    WHERE contains (*, 'chicago and Boston and "New York"' )

  • July 26, 2013 at 2:56 pm

    #1636337

    Thanks for your help! one more question related to CONTAINS. If I search for lobby is it possible to make it search for lobbies also, which is the plural of lobby? FREETEXT seems to be doing this but I want to do it with CONTAINS because it will me search with an AND clause. FREETEXT using OR clause which I don't need.

    For example if I do below search for 'Hotel AND lobby', I would like to get values that have both Hotel and lobby/lobbies

    WHERE contains (*, 'Hotel AND lobby' )

  • July 26, 2013 at 3:23 pm

    #1636346

    sql server developer (7/26/2013)

    Thanks for your help! one more question related to CONTAINS. If I search for lobby is it possible to make it search for lobbies also, which is the plural of lobby? FREETEXT seems to be doing this but I want to do it with CONTAINS because it will me search with an AND clause. FREETEXT using OR clause which I don't need.

    For example if I do below search for 'Hotel AND lobby', I would like to get values that have both Hotel and lobby/lobbies

    WHERE contains (*, 'Hotel AND lobby' )

    Answered in your other thread. http://www.sqlservercentral.com/Forums/FindPost1478218.aspx

    It's best not to cross-post since some of us are easily confused. :hehe:

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply