Adding Quotations to strings

  • Hello All,

    This may have been addressed in the past, and probably done better, but I thought I'd share.

    I recently had a problem where I had to write a big case statement based on what some moron had put together in an excel file. For reasons that I do not want to go into, I couldn't just use the file to create a temp table and use that. So I was having to say CASE WHEN blah IN (*loads of values*) THEN 'blah'.

    This would mean having to do some stuff in excel to fit quotation marks around the values or manually typing the quotes. I hate excel so I decided to come up with a solution using SQL. Here is what I did:

    DECLARE @Stringcsv VARCHAR(100)

    DECLARE @StringCR VARCHAR(1000)

    SET @stringcsv = 'a,b,c'

    SET @StringCR = 'Whatever

    Whenever'

    SELECT

    '''' + Replace(@stringcsv, ',', ''',''') + ''''

    SELECT

    Replace('''' + Replace(@StringCR, Char(13), ''',''') + '''', Char(10), '')

    As you can see I have catered for comma separated values as excel rows with a carriage return.

    Works well. Just thought someone may find it useful 🙂

    Let the flaming begin! 😀


    I'm on LinkedIn

  • You may parse the string into a table and search for values in there.

    Search this site for "split function" and choose the one you like the most.

    You may put the outcome into a temp table or search straight in the function outcome:

    SELECT * from Table where ValueInColumn in (select Value from dbo.SplitFunction (@String))

    _____________
    Code for TallyGenerator

  • Good point.

    This was a very quick and dirty solution 🙂


    I'm on LinkedIn

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

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