Suppose sometimes we are saving HTML string into the database(SQL Server).
Now when we want to display some limited characters on the web page like first 200 char,
return string will not be proper HTML as it will leave some open HTML tag into it and hense it will not render HTML properely on the web page and also disturb our page html.
So to avoide this problem we can strip html from the sql server. I have written an SQL UDF for that as below.
Following UDF takes input as HTML and returns TEXT only.
If there is any single quotes in HTML then it must be replaced with two single quotes while passing to the function as a input.
Create Function [dbo].[StripHTML]
(@HTMLString VarChar(Max))
Returns Varchar(Max)
As
Begin
Declare @StartChar Int
Declare @EndChar Int
Declare @Length Int
Set @StartChar = CharIndex('<',@HTMLString)
Set @EndChar = CharIndex('>',@HTMLString,CharIndex('<',@HTMLString))
Set @Length = (@EndChar - @StartChar) + 1
While @StartChar > 0 AND @EndChar > 0 AND @Length > 0
Begin
Set @HTMLString = Stuff(@HTMLString,@StartChar,@Length,'')
Set @StartChar = CharIndex('<',@HTMLString)
Set @EndChar = CharIndex('>',v,CharIndex('<',@HTMLString))
Set @Length = (@EndChar - @StartChar) + 1
End
Return LTrim(RTrim(@HTMLString))
End
Go
Example:
Declare @HTMLString As VarChar(Max) = '<table class="tempwrapper"> '+
'<tr><td colspan="3">.NET that will convert a string so that it can be eg I would like to convert the string "a < b" '+
'to "a C; b".</td> </tr> </table>'
Select SUBSTRING(@HTMLString,0,100)
Select SUBSTRING([dbo].[StripHTML](@HTMLString),0,100)
-> <table class="tempwrapper"> <tr><td colspan="3">.NET that will convert a string so that it can be e
-> .NET that will convert a string so that it can be eg I would like to convert the string "a <
Here I have attached a screen shot of the UDF at action.
Happy Programming :-)
Now when we want to display some limited characters on the web page like first 200 char,
return string will not be proper HTML as it will leave some open HTML tag into it and hense it will not render HTML properely on the web page and also disturb our page html.
So to avoide this problem we can strip html from the sql server. I have written an SQL UDF for that as below.
Following UDF takes input as HTML and returns TEXT only.
If there is any single quotes in HTML then it must be replaced with two single quotes while passing to the function as a input.
Create Function [dbo].[StripHTML]
(@HTMLString VarChar(Max))
Returns Varchar(Max)
As
Begin
Declare @StartChar Int
Declare @EndChar Int
Declare @Length Int
Set @StartChar = CharIndex('<',@HTMLString)
Set @EndChar = CharIndex('>',@HTMLString,CharIndex('<',@HTMLString))
Set @Length = (@EndChar - @StartChar) + 1
While @StartChar > 0 AND @EndChar > 0 AND @Length > 0
Begin
Set @HTMLString = Stuff(@HTMLString,@StartChar,@Length,'')
Set @StartChar = CharIndex('<',@HTMLString)
Set @EndChar = CharIndex('>',v,CharIndex('<',@HTMLString))
Set @Length = (@EndChar - @StartChar) + 1
End
Return LTrim(RTrim(@HTMLString))
End
Go
Example:
Declare @HTMLString As VarChar(Max) = '<table class="tempwrapper"> '+
'<tr><td colspan="3">.NET that will convert a string so that it can be eg I would like to convert the string "a < b" '+
'to "a C; b".</td> </tr> </table>'
Select SUBSTRING(@HTMLString,0,100)
Select SUBSTRING([dbo].[StripHTML](@HTMLString),0,100)
-> <table class="tempwrapper"> <tr><td colspan="3">.NET that will convert a string so that it can be e
-> .NET that will convert a string so that it can be eg I would like to convert the string "a <
Here I have attached a screen shot of the UDF at action.
Happy Programming :-)
0 comments:
Post a Comment