Post reply

FTI search issue

  • October 29, 2015 at 2:39 am

    #321734

    Hi All,

    I am starting to use the full text indexing feature on SQL 2012.

    I have done all setup and configurations steps.

    I have the following SQL getting no results

    select * from naming where contains(name,' "*xin" ')

    while there is a record in table as Digoxin and other examples as well.

    The following SQL is getting 92 records

    select * from namingtrades where tradename like '%xin'

    So where is the problem here?

    The thing i am trying to achieve from FTI is better performance specially for NON SARGABLE conditions.

    Thanks in advance

    Nader

  • October 29, 2015 at 2:41 am

    #1836263

    Remove the * from the CONTAINS search, do you get the right results then?

  • October 29, 2015 at 3:25 am

    #1836275

    anthony.green (10/29/2015)

    Remove the * from the CONTAINS search, do you get the right results then?

    I changed the query to select * from namingtrades where contains(tradename,' "xin" ')

    as you suggested and still no records are coming.

    I need to get words ending with xin prefix.

    Thanks

  • October 29, 2015 at 4:15 am

    #1836289

    Ah sorry I understand what your doing now.

    Your wanting a left sided prefix search which is not possible in full text, only right side eg. "xin*"

    My recommendation would be to add a computed column to namingtrades to reverse the string and then index that and pass in your string reversed also.

  • October 29, 2015 at 4:30 am

    #1836291

    I guess they did it that way to be SARGABLE, am i right?

    But then will i gain performance wise by implementing the FTI over the normal like operator?

  • October 29, 2015 at 4:34 am

    #1836294

    If you have a reversed string in a computed column and then add an index to that column

    Your string then looks like nixcba for example.

    You can then full text that column and do your contains on the prefix "nix*" or a reversed like

    select * from namingtrades where computedcolumn like REVERSE('%xin')

    This reverses the lookup so it is actually doing a search on nix% to make it sargable as the computed column is already reversed.

  • October 29, 2015 at 6:41 am

    #1836338

    anthony.green (10/29/2015)

    If you have a reversed string in a computed column and then add an index to that column

    Your string then looks like nixcba for example.

    You can then full text that column and do your contains on the prefix "nix*" or a reversed like

    select * from namingtrades where computedcolumn like REVERSE('%xin')

    This reverses the lookup so it is actually doing a search on nix% to make it sargable as the computed column is already reversed.

    Thank you anthony, i will try that.

Viewing 7 posts - 1 through 6 (of 6 total)

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