February 1, 2021 at 9:04 pm
I have a third party application that allows the user to select multiple items and out puts a string as follows 'item1,item2,item3'. I know this is very simple but i need to take that list in @list = 'item1,item2,item3' and transform it to @listpar = ('item1,'item2',item3'). i am having a brain cramp.
February 1, 2021 at 9:51 pm
Like this?
DECLARE @List VARCHAR(50) = 'item1,item2,item3';
SELECT
@List
, NewVersion = CONCAT('(', '''', REPLACE(@List, ',', ''','''), '''', ')');
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 1, 2021 at 10:24 pm
If you want to use that string with IN - as in :
WHERE column IN (@listpar)
Then formatting the string isn't going to work. You need to parse the elements of the list into a table - and then it can be utilized. You can get a pretty good string split utility here: https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function
Make sure you also look at this: https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2
It is an update to the original utility that provide a bit of a performance improvement.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 1, 2021 at 10:57 pm
not sure if it was just a typo but the example supplied is only adding a single quote to the item on the middle - so clarification on the requirements needs to be given by the OP.
('item1,'item2',item3')
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply