Post reply

Pass array/csv into stored proc and doing LIKE statements

  • October 17, 2008 at 11:36 am

    #73062

    HI,

    This seems like a pretty simple request but i don't find an easy way to accomplish this in SQL.

    I have a stored proc that needs to accept and array of strings and do a 'like' search for each item in the array.

    for example, if the array had 3 items (companyA, companyB, companyC) the query should look something like this:

    select * from dbo.Company where

    companyName LIKE '%companyA%'

    OR companyName LIKE '%companyB%'

    OR companyName LIKE '%companyC%'

    can someone provide me the T-SQL to accomplish this ?

    Much thanks in advance!

    Doug

  • October 17, 2008 at 4:24 pm

    #886701

    First you need to convert csv to table variable.

    Search this site for "split" or "parse" function, there are plenty of them.

    Then you join that table variable to the static table:

    select C.*

    from dbo.Company C

    inner join @ParsedString T ON C.companyName LIKE '%' + T.String + '%'

    _____________
    Code for TallyGenerator

  • October 17, 2008 at 9:14 pm

    #886721

    Sergiy is correct... and here's a link for a high speed and easy method to do just about any kind of single character delimiter split you might imagine...

    http://qa.sqlservercentral.com/articles/T-SQL/63003/

    --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)
    Intro to Tally Tables and Functions

  • October 18, 2008 at 3:30 pm

    #886801

    great thanks so much for helping me with this .

  • October 18, 2008 at 6:05 pm

    #886822

    You bet... thank you for the feedback. Let us know if you have any additional problems with this... 🙂

    --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)
    Intro to Tally Tables and Functions

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

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