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 5 (of 5 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy