September 16, 2015 at 10:06 am
Hello All,
I have a table with the following data;
CREATE TABLE #Tab (Data Varchar(100))
INSERT INTO #Tab (Data)
Select 'Apple=5,Orange=10,Banana=11' UNION ALL
Select 'Apple=10,Orange=1033,Banana=0' UNION ALL
Select 'Apple = 120,Orange = 1,Banana = 112'
Select * from #Tab
How do I replace every value before the '=' but leave the comma.
Here is what the final output should look like
CREATE TABLE #TabFinal (Data Varchar(100))
INSERT INTO #TabFinal (Data)
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana'
Select * from #TabFinal
Thanks you for reading!
September 16, 2015 at 10:09 am
Are there always three fruits, or does that quantity vary row to row?
September 16, 2015 at 10:11 am
There could be more than three fruits. Very curious to see why you asked that.
September 16, 2015 at 10:12 am
Also, is there significance to the extra spaces in the 3rd row of data? (Before and after the '=')
September 16, 2015 at 10:13 am
No significance. Just wanted to make it vary.
September 16, 2015 at 10:31 am
SELECT STUFF(
(SELECT ','+LTRIM(RTRIM(LEFT(Item, CHARINDEX('=', Item + '=') - 1)))
FROM dbo.DelimitedSplit8K (t.data, ',') ds
FOR XML PATH('')),
1, 1, '')
FROM #Tab t
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 16, 2015 at 10:34 am
You can find the DelimitedSpli8k function that Scott used (along with its explanation and benchmark) in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
And the explanation for the concatenation method using FOR XML PATH in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
September 16, 2015 at 10:42 am
Thanks, this worked fine for what I was trying to do. I appreciate it.
For my own further knowledge though, what if the data was mixed with some strings like the following example.
CREATE TABLE #Tab (Data Varchar(100))
INSERT INTO #Tab (Data)
Select 'Apple=5,Orange=''10,11,12'',Banana=11' UNION ALL
Select 'Apple=10,Orange=''1033,2,2'',Banana=0' UNION ALL
Select 'Apple = 120,Orange = ''1,1'',Banana = 112'
Select * from #Tab
It gets tricky because splitting it with a comma might not work. How do you go about it then?
Final output should remain the same.
CREATE TABLE #TabFinal (Data Varchar(100))
INSERT INTO #TabFinal (Data)
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana'
Select * from #TabFinal
Thanks
September 16, 2015 at 11:37 am
If you're going to allow that, easiest would be to then quote all the values:
Select 'Apple=''5'',Orange=''10,11,12'',Banana=''11''' UNION ALL
Then you can use the same technique with a delimiter of ''',' instead of just ','.
Otherwise you'd have to make adjustments to the code. One way in this case might be to split on a like pattern of ,[a-z] that is, only commas followed by a letter.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 16, 2015 at 11:48 am
It gets tricky, but you could analyze your data to find the patterns.
SELECT Data,
(SELECT LTRIM( RTRIM(RIGHT( Item, CHARINDEX( ',', REVERSE(Item) + ','))))
FROM dbo.DelimitedSplit8K( t.Data, '=')
WHERE Item LIKE '%[A-Za-z]%'
ORDER BY ItemNumber
FOR XML PATH(''))
FROM #Tab t
September 16, 2015 at 11:53 am
I'm sorry, I missed the simple option (add a WHERE clause to the initial solution).
SELECT STUFF(
(SELECT ','+LTRIM(RTRIM(LEFT(Item, CHARINDEX('=', Item + '=') - 1)))
FROM dbo.DelimitedSplit8K (t.data, ',') ds
WHERE Item LIKE '%=%'
ORDER BY ItemNumber
FOR XML PATH('')),
1, 1, '')
FROM #Tab t
September 16, 2015 at 1:41 pm
Worked like a charm. Learnt some new things today.
Thanks everyone!
September 16, 2015 at 8:25 pm
I'm a little late to the party but let's not forget about PatExclude8K[/url].
Here's two Patexclude8K solutions:
SELECT Data = NewString
FROM #Tab
CROSS APPLY dbo.PatExclude8K(data,'[= 0-9]');
SELECT Data = NewString
FROM #Tab
CROSS APPLY dbo.PatExclude8K(data,'[^a-zA-z,]');
-- Itzik Ben-Gan 2001
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy