April 26, 2011 at 10:39 am
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! 😀
April 26, 2011 at 4:28 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply