Post reply

Condensing Multiple LIKE Conditions

  • July 23, 2007 at 8:34 am

    #117395

    Hello,

    In a WHERE clause, I have the following

    WHERE [Region Name] LIKE 'bfs%'

              OR

              [Region Name] LIKE 'can%'

              OR

              [Region Name] LIKE 'Eas%'

    Is there any way I could use IN to condense this? The characters that can come back from the wildcard result can be alpha, numeric, and non alplanumeric.

    Thank you for your help!

    CSDunn

  • July 23, 2007 at 8:44 am

    #721538

    Given the example, it looks like you need it just as written.

    (in any case an IN clause is expanded internally to OR's anyway)

    /Kenneth

  • July 23, 2007 at 9:56 am

    #721562

    You could write 3 queries and union them. Not sure it would be faster, but possibly. The LIKE is not that efficient compare to other techniques.

    Perhaps you could pull off those characters into a computer column and do an exact search?

  • July 23, 2007 at 12:51 pm

    #721626

    You can put those conditions in a table and then perform a join to that table using something like:

    select x.*

    from x join y on x.[Region Name] Like y.pattern

    Cheers !


    * Noel

  • July 23, 2007 at 1:32 pm

    #721648

    Thanks for your responses!

    CSDunn

  • July 23, 2007 at 3:47 pm

    #721712

    What are those regions?

    What does that set means?

    Lets assume it's a set to be returned for some report.

    Then create a table:

    |ReportName|Region_Name|

    | MyReport |bfs|

    | MyReport |Canada|

    | MyReport |East Coast|

    | OtherReport |Canada|

    | OtherReport |East Coast|

    .....

    Then you may use

    SELECT ...

    FROM ...

    INNER JOIN dbo.RegionsInReport RR ON RR.ReportName = 'MyReport'

    _____________
    Code for TallyGenerator

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

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