Dynamic, Nested Replace

Question

I'm using SQL Server 2008 and need to strip out quite a bit of data within a string. Because of the nature and variability of the string, I think I'm needing to use multiple, nested REPLACE commands. The problem is each REPLACE needs to build on the previous one. Here is a sample of what I'm looking at:

<Paragraph><Replacement Id="40B"><Le><Run Foreground="#FFFF0000">Treatment by </Run></Le><Op isFreeText="True"><Run Foreground="#FFFF0000">test</Run></Op><Tr><Run Foreground="#FFFF0000">.  </Run></Tr></Replacement></Paragraph>

Essentially, I need it to return just the text outside of the <> brackets so for this example it would be:

Treatment by test.

Also, I wanted to mention that the strings inside the <> brackets can vary quite a bit for each row both by content and length, but it isn't relevant for what I'm needing other than making it more complex for replacing.

Here is what I've tried:

REPLACE(note,substring(note,patindex('<%>',note),CHARINDEX('>',note) - CHARINDEX('<',note) + 1),'')

And it returns:

<Replacement Id="40B"><Le><Run Foreground="#FFFF0000">Treatment by </Run></Le><Op isFreeText="True"><Run Foreground="#FFFF0000">test</Run></Op><Tr><Run Foreground="#FFFF0000">.  </Run></Tr></Replacement></Paragraph>

Somehow I need to keep going with replacing each of the <> brackets but don't know how to proceed. Any help or guidance would be greatly appreciated!!!

You don't want to do this in SQL. You don't want to do this in regex. cf https://stackoverflow.com/questions/1732348/regex-match-open-tags-except-xhtml-self-contained-tags/1732454#1732454 — Hogan, Nov 10 '17 at 18:48
@JacobH -- personally I think it should be added to the hot links below the tags. :D — Hogan, Nov 10 '17 at 18:55
@rob -- to be fair to you -- sql-server has a built in xml parser -- use that. — Hogan, Nov 10 '17 at 18:56
I'm trying to use this code in a SQL Server table trigger to insert data into another table. — Rob, Nov 10 '17 at 22:08

score 0 · Accepted Answer · answered Nov 11 '17 at 00:13

Depending on how you have that string holding the HTML fragment available you could try to use something like:

SELECT convert(xml, '<Paragraph><Replacement Id="40B"><Le><Run Foreground="#FFFF0000">Treatment by </Run></Le><Op isFreeText="True"><Run Foreground="#FFFF0000">test</Run></Op><Tr><Run Foreground="#FFFF0000">.  </Run></Tr></Replacement></Paragraph>').value('/', 'varchar(255)') as stripped

You convert it to XML and then use the built in xml parser function "value".

That did it! Thank you so much! Now I just need to figure out what all of that means! — Rob, Nov 13 '17 at 18:36

Dynamic, Nested Replace

1 Answers1