Dynamic String Replacement

Question

Post reply

Dynamic String Replacement

npatel565

SSC Veteran

Points: 202
More actions
September 16, 2015 at 10:06 am

#303179

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!

Viewing 13 posts - 1 through 12 (of 12 total)

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

LoudClear SSC Eights! Points: 999 More actions · Answer 1

Are there always three fruits, or does that quantity vary row to row?

npatel565 SSC Veteran Points: 202 More actions · Answer 2

There could be more than three fruits. Very curious to see why you asked that.

LoudClear SSC Eights! Points: 999 More actions · Answer 3

Also, is there significance to the extra spaces in the 3rd row of data? (Before and after the '=')

npatel565 SSC Veteran Points: 202 More actions · Answer 4

npatel565

SSC Veteran

Points: 202

September 16, 2015 at 10:13 am

#1827136

No significance. Just wanted to make it vary.

ScottPletcher SSC Guru Points: 101105 More actions · Answer 5

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".

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

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/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

npatel565 SSC Veteran Points: 202 More actions · Answer 7

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'

Select * from #TabFinal

Thanks

ScottPletcher SSC Guru Points: 101105 More actions · Answer 8

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".

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

npatel565 SSC Veteran Points: 202 More actions · Answer 11

Worked like a charm. Learnt some new things today.

Thanks everyone!

Alan Burstein SSC Guru Points: 61141 More actions · Answer 12

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,]');

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001