Post reply

full text search to match pipe separated data

  • April 28, 2010 at 5:59 am

    #140090

    I have a column with datatype nVarchar which contains pipe separated data like 1|10|11|5|4.

    Is there any way to match exact number(s) using full text search or any other way?

    If I search for 1 it should return only 1 not 10 or 11 as "Like" keyword returns records with 1,10,11.

    I am storing in this way to avoid join query which is reducing speed.

  • April 28, 2010 at 9:35 am

    #1159059

    SandeepGupta (4/28/2010)

    I have a column with datatype nVarchar which contains pipe separated data like 1|10|11|5|4.

    Is there any way to match exact number(s) using full text search or any other way?

    If I search for 1 it should return only 1 not 10 or 11 as "Like" keyword returns records with 1,10,11.

    where '|' + column + '|' like '%|' + convert(varchar(15), @NumberPassedIn) + '|%'

    I am storing in this way to avoid join query which is reducing speed.

    Very very bad design. This will reduce speed even more... now you have to do a table scan on the field in order to be able to extract specific information since you can't utilize an index on this column. Normalize it into it's own table, and with proper indexes it will be magnitudes faster!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 2 posts - 1 through 2 (of 2 total)

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