Post reply

Escaping special characters Full text index search

  • January 16, 2008 at 2:22 pm

    #123121

    I have created a full text index on a column of a table. The issue is i need to search for AT&T.

    the query i use for search is

    SELECT * FROM TABLE_NAME WHERE CONTAINS(COLUMN_NAME, '"AT&T*"')

    It returns many records but doesn't return the records with AT&T in it. I guess '&' is killing it, but i have no idea how to escape it in case of full text index search.

    Any help will be deeply appreciated.

  • July 8, 2012 at 4:51 am

    #1510074

    The '*' is killing you. Use '%' instead.

    If you want to search for text that for example contains % or other special characters you have to escape them.

    See the example below:

    SELECT * FROM

    (

    SELECT UserName + '&%' + Email AS A FROM Employees

    ) G

    WHERE G.A LIKE '%s&!%x%' ESCAPE '!'

    I am concatenating two fields adding &% in between. No escaping needed there.

    And I want to look for any record that has the literal pattern 's&%x' inside it

    So in the LIKE however, I start with an '%s (the equivalent of *), follow with an 's', follow with an '&' (no escaping needed) and then I escape the '%' using any character I like, so I chose '!'. Then follows 'x' and '%' for the rest of the string.

    Pretty straightforward right?

    Cheers

    Dimitris

  • July 8, 2012 at 12:27 pm

    #1510117

    dimitris.staikos (7/8/2012)

    The '*' is killing you. Use '%' instead.

    Keep in mind that this isn't standard T-SQL. It's Full Text Search.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 18, 2012 at 4:52 am

    #1514118

    Yeah, you are right, thanks 🙂

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

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