How to insert markup in string

It is based on the wonderful STUFF function, but allows any number of 'simultaneous' insertions into a string without the pain and slowness of multiple string concatenations. Let's take a slightly artificial example where you decide to render a string in several colours. We have the information as to the location of the start and end of each coloured part of the string.

DECLARE @MarkedUpString VARCHAR(255)
DECLARE @markup TABLE (start INT PRIMARY KEY, [end] INT, color VARCHAR(10))
INSERT INTO @markup (start,[end],color) SELECT  1, 5,'red'
INSERT INTO @markup (start,[end],color) SELECT  9, 10,'green'
INSERT INTO @markup (start,[end],color) SELECT  11, 17,'fuscia'
INSERT INTO @markup (start,[end],color) SELECT  36, 44,'blue'

SELECT @markedupString='This is a string which needs to be coloured'
SELECT @markedUpString=
   STUFF(
         STUFF(@MarkedUpString+'  ',[end],0,'</span>'),
         [start],0,'<span style="color:'+color+';">'
        )
FROM @markup ORDER BY start DESC
          
SELECT @markedupString
/*
<span style="color:red;">This</span> is <span style="color:green;">a</span> <span style="color:fuscia;">string</span> which needs to be <span style="color:blue;">coloured</span>        
*/

  so there you have it. Because there is no overlap, and we do the replacements from the end of the string to the beginning, it all works out, and we can use the initial references without them getting spoiled. The reason I've got so interested in this technique is because any string manipulation with a long string takes a big performance hit. This is, I think, due to the way that the NET framework handles strings. If you are handling big strings say 32K and 40K, or larger, it will pay you to use an approach which cuts down on unnecessary string manipulation, and you can thereby save a lot of time. My advice would be to feel free with small strings below 32K in SQL Server, but avoid too much processing on any string variable larger where possible.
By C_A P   Popularity  (809 Views)