SQL Server change font in html string

Question

I have a strings stored in my database formatted as html, and users can change the font size. That's fine, but I need to make a report and the font sizes all need to be the same. So, if I have the following html, I want to modify it to have a font size of 10:

<HTML><BODY><DIV STYLE="text-align:Left;font-family:Tahoma;font-style:normal;font-weight:normal;font-size:11;color:#000000;"><DIV><DIV><P><SPAN>This is my text to display.</SPAN></P></DIV></DIV></DIV></BODY></HTML>

I have a user defined function, but apparently, I can't use wildcards in a REPLACE, so it doesn't actually do anything:

ALTER FUNCTION [dbo].[udf_SetFont]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN REPLACE (@HTMLText, 'font-size:%;', 'font-size:10;')
END

(Of course, it would be even better if I sent the font size as a parameter, so I could change it to whatever.)

How do I modify this to change any string so the font size is 10?

Have you considered using CSS classes and keeping the presentation formatting in an external CSS file? — recursive
– recursive, Commented Jun 16, 2011 at 18:52
Any solution here is going to struggle since T-SQL is bad at string parsing. My suggestion would be to write a CLR function so you can take advantage of the .NET string libraries. I'll post what I've come up with so far, which admittedly isn't much. — Yuck
– Yuck, Commented Jun 16, 2011 at 19:10
The reporting is done in Crystal Reports, and they want the html formatting, mostly. I don't have control over how the data is stored. — thursdaysgeek
– thursdaysgeek, Commented Jun 16, 2011 at 19:38
I added code as an answer that appears to work, but I don't want to accept my own answer until I get some code reviews. I don't know that there isn't something wrong that I just haven't encountered yet. — thursdaysgeek
– thursdaysgeek, Commented Jun 16, 2011 at 23:45

thursdaysgeek · Accepted Answer · 2011-06-16 23:43:14Z

This appears to work, although I've only tried it on one string (which has the font set in 2 places). I started with code that strips ALL html and modified it to only look for and change 'font-size:*'. I suspected there would be issues if the font size is 9 or less (1 character) and I'm changing it to 10 (2 chars), but it seems to work for that too.

ALTER FUNCTION [dbo].[udf_ChangeFont]
(@HTMLText VARCHAR(MAX), @FontSize VARCHAR(2))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX('font-size:',@HTMLText)
SET @End = CHARINDEX(';',@HTMLText,CHARINDEX('font-size:',@HTMLText))
SET @Length = (@End - @Start) + 1

WHILE @Start > 0
AND @End > 0
AND @Length > 0
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'font-size:' + @FontSize + ';')
SET @Start = CHARINDEX('font-size:',@HTMLText, @End+2)
SET @End = CHARINDEX(';',@HTMLText,CHARINDEX('font-size:',@HTMLText, @End+2))
SET @Length = (@End - @Start) + 1
END
RETURN LTRIM(RTRIM(@HTMLText))
END

Yuck · Accepted Answer · 2011-06-16 19:12:51Z

DECLARE @HTML NVarChar(2000) = '
  <HTML>
    <BODY>
      <DIV STYLE="text-align:Left;font-family:Tahoma;font-style:normal;font-weight:normal;font-size:11;color:#000000;">
      <DIV>
      <DIV>
        <P><SPAN>This is my text to display.</SPAN></P>
      </DIV>
      </DIV>
      </DIV>
    </BODY>
  </HTML>';
DECLARE @X XML = @HTML;

WITH T AS (
  SELECT C.value('.', 'VarChar(1000)') StyleAttribute
  FROM @X.nodes('//@STYLE') D(C)
)
SELECT *
FROM T
WHERE T.StyleAttribute LIKE '%font-size:%';

From here I'd use a CLR function to split the StyleAttribute column on ;. Then look for the piece(s) that begin with font-size: and split again on :. TryParse the second element of that result and if it isn't 10, replace it. You'd then build up your string to get the value that StyleAttribute should have. From there you can do a REPLACE looking for the original value (from the table above) and substituting the output of the CLR function.

Nasty problem...good luck.

Shan Plourde · Accepted Answer · 2011-06-16 19:29:09Z

0

As Yuck said, SQL Server string functions pretty limited. You'll eventually run into a wall where your best bet is to resort to non-SQL solutions.

If you absolutely need to store HTML with embedded styles are you currently have, but also have the flexibility to revise your data model, you might want to consider adding a second database column to your table. The second column would store the style-free version of the HTML. You could parse out the styling at the application layer. That would make it a lot easier to view the contents in future reports and other scenarios.

answered Jun 16, 2011 at 19:29

Shan Plourde

8,7362 gold badges33 silver badges42 bronze badges

Collectives™ on Stack Overflow

SQL Server change font in html string

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related