Post reply

Search String(s) within a String best practice?

  • December 9, 2013 at 6:57 am

    #285754

    Hi All,

    I have a simple task to perform: to return all the records, where myColumn field has 'value1', 'value2' or 'value3'.

    SELECT *

    FROM myTable

    WHERE myColumn IN ('value1', 'value2','value3')

    did the job perfectly for ages.

    BUT now this myColumn can be populated by 3rd party with different / additional values, e.g. 'value1ABCD' or 'value3123'.

    To overcome this problem I have used LIKE and OR as below, but I am not sure how good this approach is:

    SELECT *

    FROM myTable

    WHERE myColumn LIKE 'value1%'

    OR myColumn LIKE 'value2%'

    OR myColumn LIKE 'value3%'

    it obviously works well, but anything you would advise me to try instead?

    I am not asking for a solution, so am not providing with working examples to test, but am looking for guidelines - what is the best approach

    in such a case?

    Massive Thanks!

  • December 9, 2013 at 8:58 am

    #1672004

    Maybe a Full Text Index might help?

    http://technet.microsoft.com/en-us/library/ms142571.aspx

  • December 9, 2013 at 9:10 am

    #1672017

    Thank you for the answer!

    But the customer does't use Full Text Index on their databases, I am afraid.

  • December 9, 2013 at 9:16 am

    #1672019

    If you can, I'd consider a computed column with index. Maybe an indexed view trimming the values could work for you.

    Otherwise I believe there is little you can with query to achieve what you want without some performance impact. I could be wrong but I can't think of an alternative

  • December 9, 2013 at 4:09 pm

    #1672163

    As long as you have fixed leading values, i.e. 'value1%' and not '%value1%',

    an index on myColumn should handle the query fine. If it's a large % of the total rows, a table scan may be inevitable anyway.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 9, 2013 at 4:16 pm

    #1672166

    Thank you all for the answers!

  • January 1, 2014 at 1:00 pm

    #1676762

    ScottPletcher (12/9/2013)

    As long as you have fixed leading values, i.e. 'value1%' and not '%value1%',

    an index on myColumn should handle the query fine. If it's a large % of the total rows, a table scan may be inevitable anyway.

    +1000

    --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)

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

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