Post reply

Using Full-Text Search

  • November 20, 2012 at 9:38 am

    #1560889

    SpringTownDBA (11/19/2012)

    Full text querying only does prefix matching, not postfix

    'erin*' matches 'erinblah'

    '*erin' doesn't match 'blaherin'

    There is no way to match the last half of a word.

    Actually, the way around this is to use a calculated field that reverses the string, and include both columns in the FTS. Then search one field with your pattern, and search the other with the reverse pattern, intersecting the 2 queries.

    Works great 🙂

  • November 20, 2012 at 9:48 am

    #1560894

    DiverKas (11/20/2012)

    SpringTownDBA (11/19/2012)

    Full text querying only does prefix matching, not postfix

    'erin*' matches 'erinblah'

    '*erin' doesn't match 'blaherin'

    There is no way to match the last half of a word.

    Actually, the way around this is to use a calculated field that reverses the string, and include both columns in the FTS. Then search one field with your pattern, and search the other with the reverse pattern, intersecting the 2 queries.

    Works great 🙂

    +1, Good and simple idea!

    However it still can not replace LIKE '%anason% to search for "Panasoinc"...

    Actually, Google advises for "panasonic", but first few pages of results doesn't display any entry matching it as there are quite few matching "anason";-)

    Personally, I don't think that there is a real value in LIKE '%text%' search at all...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • November 20, 2012 at 9:49 am

    #1560895

    DiverKas (11/20/2012)

    SpringTownDBA (11/19/2012)

    Full text querying only does prefix matching, not postfix

    'erin*' matches 'erinblah'

    '*erin' doesn't match 'blaherin'

    There is no way to match the last half of a word.

    Actually, the way around this is to use a calculated field that reverses the string, and include both columns in the FTS. Then search one field with your pattern, and search the other with the reverse pattern, intersecting the 2 queries.

    Works great 🙂

    That's an interesting approach to handle postfix matching.

    That leaves the last case, matching 'xxxAAAyyy' when searching for '*AAA*'

  • November 20, 2012 at 10:05 am

    #1560906

    SpringTownDBA (11/20/2012)

    DiverKas (11/20/2012)

    SpringTownDBA (11/19/2012)

    Full text querying only does prefix matching, not postfix

    'erin*' matches 'erinblah'

    '*erin' doesn't match 'blaherin'

    There is no way to match the last half of a word.

    Actually, the way around this is to use a calculated field that reverses the string, and include both columns in the FTS. Then search one field with your pattern, and search the other with the reverse pattern, intersecting the 2 queries.

    Works great 🙂

    That's an interesting approach to handle postfix matching.

    That leaves the last case, matching 'xxxAAAyyy' when searching for '*AAA*'

    Which, unfortunately, is nigh impossible currently with FTS, since it is tokenized for words, as words are the smallest units it considers.

Viewing 4 posts - 16 through 18 (of 18 total)

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