Skip to content Skip to sidebar Skip to footer
Home / Html / Sql Server / Sql Server 2008 / User Defined Functions

Sql Server Change Font In Html String

Post a Comment
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.

Solution 1:

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.

ALTERFUNCTION [dbo].[udf_ChangeFont]
(@HTMLTextVARCHAR(MAX), @FontSizeVARCHAR(2))
RETURNSVARCHAR(MAX)
ASBEGINDECLARE@StartINTDECLARE@EndINTDECLARE@LengthINTSET@Start= CHARINDEX('font-size:',@HTMLText)
SET@End= CHARINDEX(';',@HTMLText,CHARINDEX('font-size:',@HTMLText))
SET@Length= (@End-@Start) +1

WHILE @Start>0AND@End>0AND@Length>0BEGINSET@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) +1ENDRETURN LTRIM(RTRIM(@HTMLText))
END

Solution 2: 

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 textto display.</SPAN></P>
      </DIV>
      </DIV>
      </DIV>
    </BODY>
  </HTML>';DECLARE @X XML = @HTML;

WITH T AS (
  SELECT C.value('.', 'VarChar(1000)') StyleAttributeFROM @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.

Solution 3:

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.

Post a Comment for "Sql Server Change Font In Html String"