Adding Quotations to strings

• PB_BI

  SSCoach

  Points: 17471

  More actions

  April 26, 2011 at 10:39 am

  #235227

  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

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  April 26, 2011 at 4:28 pm

  #1316313

  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

• PB_BI

  SSCoach

  Points: 17471

  More actions

  April 27, 2011 at 2:04 am

  #1316399

  Good point.

  This was a very quick and dirty solution

  ---

  I'm on LinkedIn