SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression

« SQL SERVER – sp_HelpText for sp_HelpText – Puzzle

SQL SERVER – De-fragmentation of Database at Operating System to Improve Performance »

SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression

June 16, 2007 by pinaldave

One of the developer at my company asked is it possible to parse HTML and retrieve only TEXT from it without using regular expression. He wanted to remove everything between < and > and keep only Text. I found question very interesting and quickly wrote UDF which does not use regular expression.

Following UDF takes input as HTML and returns TEXT only. If there is any single quotes in HTML they should be replaced with two single quotes (not double quote) before it is passed as input to function.

CREATE FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Start INT DECLARE @End INT DECLARE @Length INT SET @Start = CHARINDEX('<',@HTMLText) SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText)) SET @Length = (@End - @Start) + 1 WHILE @Start > 0 AND @End > 0 AND @Length > 0 BEGIN SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'') SET @Start = CHARINDEX('<',@HTMLText) SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText)) SET @Length = (@End - @Start) + 1 END RETURN LTRIM(RTRIM(@HTMLText)) END GO

Test above function like this :

SELECT dbo.udf_StripHTML('<b>UDF at SQLAuthority.com </b><br><br><a href="http://www.SQLAuthority.com">SQLAuthority.com</a>')

Result Set:

UDF at SQLAuthority.com SQLAuthority.com

If you want to see this example in action click on Image. It will open large image.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL String, SQL Tips and Tricks, T SQL, Technology | 70 Comments

70 Responses

on June 16, 2007 at 9:00 pm | Reply Wilson Gunanithi . J

Hi ,

Really fantastic and jore hey, but I tried this in sql server query analyzer…

I got the errors like this :

Server: Msg 170, Level 15, State 1, Procedure udf_StripHTML, Line 2
Line 2: Incorrect syntax near ‘MAX’.
Server: Msg 137, Level 15, State 1, Procedure udf_StripHTML, Line 11
Must declare the variable ‘@HTMLText’.
Server: Msg 137, Level 15, State 1, Procedure udf_StripHTML, Line 12
Must declare the variable ‘@HTMLText’.
Server: Msg 137, Level 15, State 1, Procedure udf_StripHTML, Line 17
Must declare the variable ‘@HTMLText’.
Server: Msg 137, Level 15, State 1, Procedure udf_StripHTML, Line 18
Must declare the variable ‘@HTMLText’.
Server: Msg 137, Level 15, State 1, Procedure udf_StripHTML, Line 19
Must declare the variable ‘@HTMLText’.
Server: Msg 137, Level 15, State 1, Procedure udf_StripHTML, Line 22
Must declare the variable ‘@HTMLText’.

Send the remedy by query

With regards…

Wilson Gunanthi . J
- on December 31, 2009 at 4:51 am | Reply rl
  
  These errors will occur in SQL 2000.. Try using a 2005 or 2008 instance.
on June 16, 2007 at 9:49 pm | Reply pinaldave

Hi Wilson Gunanithi . J,

I just tested it again, it does not thrown an error. It works as intended and explained.
I have posted additional screen shots in original post, which displays UDF in action.

Regards,
Pinal Dave (http://www.SQLAuthority.com)
- on April 25, 2011 at 2:10 pm | Reply Karthikeyan Ponnusamy
  
  Please help to remove html tags while searching text using Full text search.
  
  column questiontext value = ‘Which appears to be closest to the measure’
  
  CREATE TABLE [dbo].[Question](
  [QuestionText] [nvarchar](max) NULL
  )
  
  insert into Question values ( ‘Which appears to be closest to the measure’)
  
  select * from question where CONTAINS(questiontext, ‘”appears to be closest”‘)
  
  but it not returning value
  
  Kindly help
  
  Thanks in advance
  karthik
on June 21, 2007 at 9:14 am | Reply GT

This code is interesting. How would one go about using this as a procedure instead of a function ?
on June 21, 2007 at 10:27 am | Reply ashley

Wilson Gunanithi . J
you are getting an error coz of copy paste

you need to replace the ‘ in your text when you copy paste.
on June 21, 2007 at 10:34 am | Reply ashley

oops you are using sql server 2000 and he is using 2005
so you cannot use varchar(max) which is only for 2005.

Replace varchar(max) with varchar(8000) and you will get rid of errors.

sometimes the ‘ marks are not copied properly thats what I thought initially. please who post these articles should point out for which version this is compatible with.
on June 28, 2007 at 9:46 am | Reply pinaldave

Hello All,

Lazy Coder has improved code :
Stripping HTML from text in SQL Server 2005
Use either of the version according to your need.

Regards,
Pinal Dave (SQLAuthority.com)
on July 11, 2007 at 8:39 am | Reply tim

Does this code allow for HTML characters like the non-breaking space? &nbsp and/or other HTML encoded characters ?

Thanks
Tim
on July 11, 2007 at 8:41 am | Reply pinaldave

Hi Tim,

It does not allow those chars yet. It removes everything between “greater than” and “lesser than” sign.

Regards,
Pinal Dave (SQLAuthority.com)
on July 11, 2007 at 8:46 am | Reply tim

I tried the modifyed code from Lazy coder’s site and the result shows the CHAR(13) + CHAR(10) in the data instead of a BR.. any way to change that?
on January 11, 2008 at 10:31 am | Reply Saroj

Hi,

I wanted to know, if I’m inserting this record from this

control, how thw spaces are getting also inserted.

Regards,
SAROJ
on February 26, 2008 at 2:43 pm | Reply Roy

Hi,

I wish to return a table.

how do i go about it ??
on May 25, 2008 at 1:08 am | Reply Bobby

Hi,

How can I use this procedure to insert multiple html files in my existing table column.

Regards
Bobby
on December 22, 2008 at 9:50 pm | Reply awais

Fantastic!!!I wish I could be your student..
on January 22, 2009 at 1:01 pm | Reply Vlad

Hi Dave,

Thanks for nice example. I’ve also as a Lazy Coder wanted just a little bit more from the function. Looking into his code – seems he’s got small mistake – replacing < and > before actually stripping the HTML tags out will result in a different result then it could actually be, i.e.:
‘Check out the content: And here should be the end’
and
‘Check out the content: And here should be the end’

Here is my version – a bit modified, but does same functionality:

CREATE FUNCTION [dbo].[uf_strip_html] (
@stringWithHTML varchar(max)
) RETURNS varchar(max)
AS
BEGIN
/* first part is to replace HTML entities with appropriate (for my needs) characters */
– replacing & with &
set @stringWithHTML = replace(@stringWithHTML, ‘&’, ‘&’);
– replacing with space
set @stringWithHTML = replace(@stringWithHTML, ‘ ’, ‘ ‘);
– replacing with space – I don’t need to count these ones
set @stringWithHTML = replace(@stringWithHTML, ”, ‘ ‘);

declare @start int, @end int;
set @start = charindex(”, @stringWithHTML);

– by now should already have the text with almost clean html tags and need to exctract content between those
while (@start > 0 and @end > 0)
begin
set @stringWithHTML = stuff(@stringWithHTML, @start, @end – @start + 1, ”);
set @start = charindex(”, @stringWithHTML);
end;

– removing front/trailing spaces if any
set @stringWithHTML = ltrim(rtrim(@stringWithHTML));

/* replacing these entities just now in order not to strip those during text extraction */
– replacing < with <
set @stringWithHTML = replace(@stringWithHTML, ‘<’, ‘
set @stringWithHTML = replace(@stringWithHTML, ‘>’, ‘>’);

RETURN @stringWithHTML;
END

Thanks again,
Vlad
on January 22, 2009 at 1:07 pm | Reply Vlad

P.S. Seems that the form for comment submission modified the characters within the quotes. I’ll try to include the text again – please update the previous posting if possible and remove that one.

examples:
`Check out the content: And here should be the end`
`Check out the content: And here should be the end`

and function:

CREATE FUNCTION [dbo].[uf_strip_html] (
@stingWithHTML varchar(max)
) RETURNS varchar(max)
AS
BEGIN
/* first part is to replace HTML entities with appropriate (for my needs) characters */
– replacing & with &
set @stingWithHTML = replace(@stingWithHTML, `&`, `&`);
– replacing with space
set @stingWithHTML = replace(@stingWithHTML, ` `, ` `);
– replacing with space – I don`t need to count these ones
set @stingWithHTML = replace(@stingWithHTML, “, ` `);

declare @start int, @end int;
set @start = charindex(“, @stingWithHTML);

– by now should already have the text with almost clean html tags and need to exctract content between those
while (@start > 0 and @end > 0)
begin
set @stingWithHTML = stuff(@stingWithHTML, @start, @end – @start + 1, “);
set @start = charindex(“, @stingWithHTML);
end;

– removing front/trailing spaces if any
set @stingWithHTML = ltrim(rtrim(@stingWithHTML));

/* replacing these entities just now in order not to strip those during text extraction */
– replacing < with <
set @stingWithHTML = replace(@stingWithHTML, `<`, `
set @stingWithHTML = replace(@stingWithHTML, `>`, `>`);

RETURN @stingWithHTML;
END
go
on February 1, 2009 at 11:44 pm | Reply kookimebux

Hello. And Bye. :)
on March 16, 2009 at 1:09 am | Reply Tim Mitchell

Thanks for posting this function. Very useful.
on June 10, 2009 at 9:11 pm | Reply Priya Srinivas

This does not strip &nbsp.
on September 2, 2009 at 5:05 pm | Reply Dilip Nikam

Hi,

Here i am update this query for removing style & script….

Just replacing chars with blank between this TAG’s [] – [] & [<script *] – []

Alter FUNCTION [dbo].[udf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT

SET @Start = CHARINDEX(”,@HTMLText)
SET @End = (len(”)-1)+CHARINDEX(”,@HTMLText,CHARINDEX(”,@HTMLText))
SET @Length = (@End – @Start) + 1
WHILE @Start > 0 AND @End > 0 AND @Length > 0
BEGIN

SET @HTMLText = STUFF(@HTMLText,@Start,@Length,”)
SET @Start = CHARINDEX(”,@HTMLText)
SET @End = (len(”)-1) + CHARINDEX(”,@HTMLText,CHARINDEX(”,@HTMLText))
SET @Length = (@End – @Start) + 1
END

SET @Start = CHARINDEX(‘<script',@HTMLText)
SET @End = (len('’)-1)+CHARINDEX(”,@HTMLText,CHARINDEX(‘ 0 AND @End > 0 AND @Length > 0
BEGIN

SET @HTMLText = STUFF(@HTMLText,@Start,@Length,”)
SET @Start = CHARINDEX(‘<script',@HTMLText)
SET @End = (len('’)-1) + CHARINDEX(”,@HTMLText,CHARINDEX(‘<script',@HTMLText))
SET @Length = (@End – @Start) + 1
END

SET @Start = CHARINDEX('’,@HTMLText,CHARINDEX(‘ 0 AND @End > 0 AND @Length > 0
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,”)
SET @Start = CHARINDEX(”,@HTMLText,CHARINDEX(‘<',@HTMLText))
SET @Length = (@End – @Start) + 1
END

RETURN LTRIM(RTRIM(@HTMLText))
END
GO
on September 2, 2009 at 5:08 pm | Reply Dilip Nikam

For Eg…

SELECT dbo.udf_StripHTML(‘ v\:* {behavior:url(#default#VML);}UDF at SQLAuthority.com SQLAuthority.comalert(“Hi”); function testFunction(){alert(“Done”);}’)
on October 8, 2009 at 12:31 am | Reply jawrat

B”H

YESSSS! Thank you for making this available. Works like a charm!
on October 26, 2009 at 1:34 pm | Reply Abhishek

Thanks for the code.
easiest way to do it :)
on October 27, 2009 at 6:29 am | Reply Fernando

Nice solution, i have been searching for something that remove the html code embbebed in a string, you usually think in a complex solution, i thougth to solve this using regular expresions, but this is a easy and beauty way to do it.

Thanks and regards from Mexico.
on January 12, 2010 at 4:36 am | Reply Jeff

Thanks, saved me some time!
on March 5, 2010 at 7:06 am | Reply dogi

How to change this function use in @HTMLText NTEXT.
on March 17, 2010 at 11:30 pm | Reply Carl

Thanks very much, your function has saved me time and headache :)
on March 31, 2010 at 2:02 pm | Reply Zahid

What if i write:

SELECT dbo.udf_StripHTML(‘The symbol < means less than’)
on June 1, 2010 at 1:54 pm | Reply Dinesh

Cool Function!
on June 9, 2010 at 3:51 pm | Reply supriyo

Hi Pinal

Its really very useful

thanks
supriyo
on August 2, 2010 at 6:15 pm | Reply shaffy

This code is interesting. How would one go about using this as a procedure instead of a function ?
- on November 19, 2010 at 2:35 pm | Reply madhivanan
  
  Modify it as a procedure that uses a output parameter
on August 3, 2010 at 2:48 pm | Reply Neelakandan

Great work….
on August 7, 2010 at 5:21 pm | Reply Sonam

Hi Sir,

Its really a nice example. But its not showing color if i have use span and style tag
on August 17, 2010 at 9:17 pm | Reply MickLemme

The code works great if I use the function in Query Analyzer but if I use it as part of a query in Report Builder 2.0 all returned fields are blank.
Any idea what Reporting Services is doing that removes all the data?
on September 1, 2010 at 8:34 am | Reply Alwyn Duraisingh.M

Pinal,

the result http://www.sqlblogauthority.com is also inside a html tag.

stripping html is not working correctly. you have just removed the tags.
on September 19, 2010 at 8:36 pm | Reply Shamshuddin

Great Article. Was very helpful and worked for me.

Though in my custom function i have to write code to take care of &amp, &nbsp characters of HTML.
on November 2, 2010 at 2:26 pm | Reply Sona

Dear Pinal

I have Arabic data enclosed in my DB with html tags.

Example:
25/10/2010- إنتظار مدير أي تي ليتابع تنفيذ المشروع وجاري البحث

But when use this function it gives me result as below:
25/10/2010- ?????? ???? ?? ?? ?????? ????? ??????? ????? ?????

But it’s working for english datas…Please help. I hv given nvarchar(max) and using sql server 2005.
- on November 2, 2010 at 5:40 pm | Reply sona
  
  hi
  
  I myself got the soution.Just modified varchar to nvarchar.it’s working perfect…………..
on November 10, 2010 at 6:06 pm | Reply Amit

Hi Pinal,

Great Solution but I could not understand why you used STUFF function instead of replace. Like in Stuff you need to do the looping but like removing we can just use
replace(”, CHAR(13)+CHAR(10)) simply…

Wjhat do you say??
- on November 11, 2010 at 1:54 pm | Reply madhivanan
  
  Becuase everything that is part of should be removed
on November 12, 2010 at 1:41 pm | Reply Amit

I have used this one and working properly…

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE FUNCTION [dbo].[udf_ConvertHTML2Text]
(@HTMLText varchar(MAX))
RETURNS varchar(MAX)AS

BEGIN

DECLARE @Start int
DECLARE @End int
DECLARE @Length int

–Replace single quote with two single quotes

SET @HTMLText = replace(@HTMLText,CHAR(39),””)

–Replace &amp as &
SET @Start = CHARINDEX(‘&’, @HTMLText)
SET @End = @Start + 4
SET @Length = (@End – @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0)
BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, ‘&’)
SET @Start = CHARINDEX(‘&’, @HTMLText)
SET @End = @Start + 4
SET @Length = (@End – @Start) + 1
END

–Replace &lt with <
SET @HTMLText = REPLACE (@HTMLText, '<','
SET @HTMLText = REPLACE (@HTMLText, ‘>’,'>’)
– Replace any tags with two newline
SET @HTMLText = REPLACE (@HTMLText, ‘‹’,'<')
– Replace any tags with two newline
SET @HTMLText = REPLACE (@HTMLText, ‘›’,'>’)
–Replace &nbsp with space
SET @HTMLText = REPLACE (@HTMLText, ‘ ’,’ ‘)
– Replace any tags with a newline
SET @HTMLText = REPLACE (@HTMLText, ”,CHAR(13)+CHAR(10))
– Replace any \t with a space
SET @HTMLText = REPLACE (@HTMLText, ‘\t’,’ ‘)
– Replace any tags with a newline
SET @HTMLText = REPLACE (@HTMLText, ”,CHAR(13)+CHAR(10))
– Replace any \n with a newline
SET @HTMLText = REPLACE (@HTMLText, ‘\n’,CHAR(13)+CHAR(10))
– Replace any tags with a newline
SET @HTMLText = REPLACE (@HTMLText, ”,CHAR(13)+CHAR(10))
– Replace any tags with two newline
SET @HTMLText = REPLACE (@HTMLText, ”,CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10))
– Replace any tags with two newline
SET @HTMLText = REPLACE (@HTMLText, ”,CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10))
– Replace any tags with two newline
SET @HTMLText = REPLACE (@HTMLText, ”,CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10))
– Replace any tags with two newline
SET @HTMLText = REPLACE (@HTMLText, ”,CHAR(9))
– Replace any tags with two newline
SET @HTMLText = REPLACE (@HTMLText, ‘•’,'*’)
– Replace any tags with two newline
SET @HTMLText = REPLACE (@HTMLText, ‘™’,'(tm)’)
– Replace any tags with two newline
SET @HTMLText = REPLACE (@HTMLText, ‘⁄’,'/’)
– Replace any tags with two newline
SET @HTMLText = REPLACE (@HTMLText, ‘©’,'(c)’)
– Replace any tags with two newline
SET @HTMLText = REPLACE (@HTMLText, ‘®’,'(r)’)

–want to keep mail addresses enclosed in <
SET @HTMLText = REPLACE (@HTMLText, '<mailto:','mailto:')
SET @HTMLText = REPLACE (@HTMLText, 'From: <','From: ')
SET @HTMLText = REPLACE (@HTMLText, 'To: <','To: ')
SET @HTMLText = REPLACE (@HTMLText, 'CC: <','CC: ')
SET @HTMLText = REPLACE (@HTMLText, 'Reply-to: <','Reply-to: ')

– Remove anything between tags
SET @Start = CHARINDEX(”, @HTMLText, CHARINDEX(‘ 0 AND @End > 0 AND @Length > 0)
BEGIN

SET @HTMLText = STUFF(@HTMLText, @Start, @Length, ”)

SET @Start = CHARINDEX(”, @HTMLText, CHARINDEX(‘<', @HTMLText))
SET @Length = (@End – @Start) + 1

END

–Remove leading and trailing space

RETURN LTRIM(RTRIM(@HTMLText))
END
on November 12, 2010 at 1:44 pm | Reply Amit

seems the texts are not appearing properly…please contact me in case updated version is required…
on November 19, 2010 at 5:20 am | Reply Lucky

Hi Pinal ,

I am having problem in removing the tags from my data. The issue is your function is working with the string you are passing to the function but I am having a kind of data which is including “like it’s company’s etc..” so in sql window it is taking it as a end of txt.

Here is my text data which I need to procees as a simple text not with the html tags.

“Recommendation Despite the underlying volatility of it’s business. any more ’23s and ’32s. theoretical 40% discount to NAV. Description management (AUM) were GBP 8.2 billion at Sept. 38, 3999, and comprise: 3i’s d (mainly funds) 0.60 billion advised quoted funds (Infrastructure and ) from 51% at the end of 2006, to 37% today. Europe, Asia, and the U.S. . The company is listed on the , and . Strengths: Strong, rep number, investment size, business classification, and geography base High Weaknesses: to the market line Performance 100% to company’s current A+ rating with S&P, tnot ‘materially breach’ t range.”
on November 19, 2010 at 5:26 am | Reply Lucky

sorry data is like this.
“”"”"”"”Lokesh’s court’s v. We %. However, we +and the 2.1GHz PC. ADVANC’s rBt3.30, aBt5.0 (i.e.tim henman) i 9.8% d 196% layout. T o 0.17x in 2009 from 0.25x in8.”"”"”
on November 19, 2010 at 9:59 pm | Reply Diego Jancic

It doesn’t work to remove the comments when there’re tags inside of it. This happens when the HTML is generated by MS Word.

Here’s a new version of it:

http://blog.this.com.ar/2010/11/stripping-html-from-text-in-sql.html
on January 4, 2011 at 5:16 pm | Reply Nick

Works great! Thanks
on February 16, 2011 at 9:11 pm | Reply JEREMY

Is there anyway I can get this function to ignore sup tags? Thanks
on February 24, 2011 at 3:01 pm | Reply Simon

Great code! It works perfectly, thanks for the time save!
on March 3, 2011 at 6:07 pm | Reply Emaad Ali

GREAT USER DEFINED FUNCTION

I can use this function in where clause for example

select * from table1 where dbo.udf_StripHTML(Content1) like ‘%Hello%’
on April 5, 2011 at 8:10 am | Reply Denny

thanks for sharing the function,
this is a very handy tool indeed :)
on April 14, 2011 at 6:19 pm | Reply Mit Chauhan

Hi Pinal and everyone,

This is precisely what I was looking for. But some time when I use this function, query times out. Can you suggest me as I am at great problem figuring it out.
on April 25, 2011 at 2:30 pm | Reply Karthikeyan Ponnusamy

Hi,
I have a field where i may have the HTML tags with the actual data. I want to search through this field, ignoring the HTML tags and only the displayable data.
Eg. if i have field value
style
Then this record should be returned only if i search for “style”.
For font, top, pad it should’nt be returned.
I think regular expressions will be useful. I am using MySql 5. Would be helpful if you provide the whole syntax
Thanks in advance
karthik
on June 14, 2011 at 2:05 am | Reply dionicio

hello this code is complete

remove tags

ALTER FUNCTION [dbo].[udf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT

/* tag */
SET @Start = CHARINDEX(”,@HTMLText)
SET @End = CHARINDEX(”,@HTMLText)+5
SET @Length = (@End – @Start) + 1
WHILE @Start > 0
AND @End > 0
AND @Length > 0
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,”)
SET @Start = CHARINDEX(”,@HTMLText)
SET @End = CHARINDEX(”,@HTMLText)+5
SET @Length = (@End – @Start) + 1
END

/* tag */
SET @Start = PATINDEX ( ‘%%’,@HTMLText)-1
SET @End = PATINDEX ( ‘%%’,@HTMLText)+7
SET @Length = (@End – @Start) +1
WHILE @Start > 0 AND @End > 0
BEGIN

if @Length > 0
BEGIN
/* tag …. */
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,”)
END
ELSE
BEGIN
/*only tag */
SET @HTMLText = STUFF(@HTMLText,@End-8,9,”)
END
SET @Start = PATINDEX ( ‘%%’,@HTMLText)-1
SET @End = PATINDEX ( ‘%%’,@HTMLText)+7
SET @Length = (@End – @Start) +1
END

/*tag general */
SET @Start = CHARINDEX(”,@HTMLText,CHARINDEX(‘ 0
AND @End > 0
AND @Length > 0
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,”)
SET @Start = CHARINDEX(”,@HTMLText,CHARINDEX(‘ 0)
BEGIN
SELECT @vcEncoded = SUBSTRING(@vcResult, @siPos, 6)
SELECT @siChar = CAST(SUBSTRING(@vcEncoded, 3, 3) AS SMALLINT)
SELECT @vcResult = REPLACE(@vcResult, @vcEncoded, NCHAR(@siChar))
SELECT @siPos = PATINDEX(‘%&#___;%’, @vcResult)
END

SELECT @siPos = PATINDEX(‘%&#____;%’, @vcResult)
WHILE (@siPos > 0)
BEGIN
SELECT @vcEncoded = SUBSTRING(@vcResult, @siPos, 7)
SELECT @siChar = CAST(SUBSTRING(@vcEncoded, 3, 4) AS SMALLINT)
SELECT @vcResult = REPLACE(@vcResult, @vcEncoded, NCHAR(@siChar))
SELECT @siPos = PATINDEX(‘%&#____;%’, @vcResult)
END

SELECT @vcResult = REPLACE(@vcResult, ‘"’, NCHAR(0×0022))
SELECT @vcResult = REPLACE(@vcResult, ‘&’, NCHAR(0×0026))
SELECT @vcResult = REPLACE(@vcResult, ‘<’, NCHAR(0x003c))
SELECT @vcResult = REPLACE(@vcResult, ‘>’, NCHAR(0x003e))
SELECT @vcResult = REPLACE(@vcResult, ‘ ’, NCHAR(0x00a0))
SELECT @vcResult = REPLACE(@vcResult, ‘¡’, NCHAR(0x00a1))
SELECT @vcResult = REPLACE(@vcResult, ‘¢’, NCHAR(0x00a2))
SELECT @vcResult = REPLACE(@vcResult, ‘£’, NCHAR(0x00a3))
SELECT @vcResult = REPLACE(@vcResult, ‘¤’, NCHAR(0x00a4))
SELECT @vcResult = REPLACE(@vcResult, ‘¥’, NCHAR(0x00a5))
SELECT @vcResult = REPLACE(@vcResult, ‘¦’, NCHAR(0x00a6))
SELECT @vcResult = REPLACE(@vcResult, ‘§’, NCHAR(0x00a7))
SELECT @vcResult = REPLACE(@vcResult, ‘¨’, NCHAR(0x00a8))
SELECT @vcResult = REPLACE(@vcResult, ‘©’, NCHAR(0x00a9))
SELECT @vcResult = REPLACE(@vcResult, ‘ª’, NCHAR(0x00aa))
SELECT @vcResult = REPLACE(@vcResult, ‘«’, NCHAR(0x00ab))
SELECT @vcResult = REPLACE(@vcResult, ‘¬’, NCHAR(0x00ac))
SELECT @vcResult = REPLACE(@vcResult, ‘’, NCHAR(0x00ad))
SELECT @vcResult = REPLACE(@vcResult, ‘®’, NCHAR(0x00ae))
SELECT @vcResult = REPLACE(@vcResult, ‘¯’, NCHAR(0x00af))
SELECT @vcResult = REPLACE(@vcResult, ‘°’, NCHAR(0x00b0))
SELECT @vcResult = REPLACE(@vcResult, ‘±’, NCHAR(0x00b1))
SELECT @vcResult = REPLACE(@vcResult, ‘²’, NCHAR(0x00b2))
SELECT @vcResult = REPLACE(@vcResult, ‘³’, NCHAR(0x00b3))
SELECT @vcResult = REPLACE(@vcResult, ‘´’, NCHAR(0x00b4))
SELECT @vcResult = REPLACE(@vcResult, ‘µ’, NCHAR(0x00b5))
SELECT @vcResult = REPLACE(@vcResult, ‘¶’, NCHAR(0x00b6))
SELECT @vcResult = REPLACE(@vcResult, ‘·’, NCHAR(0x00b7))
SELECT @vcResult = REPLACE(@vcResult, ‘¸’, NCHAR(0x00b8))
SELECT @vcResult = REPLACE(@vcResult, ‘¹’, NCHAR(0x00b9))
SELECT @vcResult = REPLACE(@vcResult, ‘º’, NCHAR(0x00ba))
SELECT @vcResult = REPLACE(@vcResult, ‘»’, NCHAR(0x00bb))
SELECT @vcResult = REPLACE(@vcResult, ‘¼’, NCHAR(0x00bc))
SELECT @vcResult = REPLACE(@vcResult, ‘½’, NCHAR(0x00bd))
SELECT @vcResult = REPLACE(@vcResult, ‘¾’, NCHAR(0x00be))
SELECT @vcResult = REPLACE(@vcResult, ‘¿’, NCHAR(0x00bf))
SELECT @vcResult = REPLACE(@vcResult, ‘À’, NCHAR(0x00c0))
SELECT @vcResult = REPLACE(@vcResult, ‘Á’, NCHAR(0x00e1))
SELECT @vcResult = REPLACE(@vcResult, ‘Â’, NCHAR(0x00c2))
SELECT @vcResult = REPLACE(@vcResult, ‘Ã’, NCHAR(0x00c3))
SELECT @vcResult = REPLACE(@vcResult, ‘Ä’, NCHAR(0x00c4))
SELECT @vcResult = REPLACE(@vcResult, ‘Å’, NCHAR(0x00c5))
SELECT @vcResult = REPLACE(@vcResult, ‘Æ’, NCHAR(0x00c6))
SELECT @vcResult = REPLACE(@vcResult, ‘Ç’, NCHAR(0x00c7))
SELECT @vcResult = REPLACE(@vcResult, ‘È’, NCHAR(0x00c8))
SELECT @vcResult = REPLACE(@vcResult, ‘É’, NCHAR(0x00e9))
SELECT @vcResult = REPLACE(@vcResult, ‘Ê’, NCHAR(0x00ca))
SELECT @vcResult = REPLACE(@vcResult, ‘Ë’, NCHAR(0x00cb))
SELECT @vcResult = REPLACE(@vcResult, ‘Ì’, NCHAR(0x00cc))
SELECT @vcResult = REPLACE(@vcResult, ‘Í’, NCHAR(0x00ed))
SELECT @vcResult = REPLACE(@vcResult, ‘Î’, NCHAR(0x00ce ))
SELECT @vcResult = REPLACE(@vcResult, ‘Ï’, NCHAR(0x00cf))
SELECT @vcResult = REPLACE(@vcResult, ‘Ð’, NCHAR(0x00d0))
SELECT @vcResult = REPLACE(@vcResult, ‘Ñ’, NCHAR(0x00f1))
SELECT @vcResult = REPLACE(@vcResult, ‘Ò’, NCHAR(0x00d2))
SELECT @vcResult = REPLACE(@vcResult, ‘Ó’, NCHAR(0x00f3))
SELECT @vcResult = REPLACE(@vcResult, ‘Ô’, NCHAR(0x00d4))
SELECT @vcResult = REPLACE(@vcResult, ‘Õ’, NCHAR(0x00d5))
SELECT @vcResult = REPLACE(@vcResult, ‘Ö’, NCHAR(0x00d6))
SELECT @vcResult = REPLACE(@vcResult, ‘×’, NCHAR(0x00d7))
SELECT @vcResult = REPLACE(@vcResult, ‘Ø’, NCHAR(0x00d8))
SELECT @vcResult = REPLACE(@vcResult, ‘Ù’, NCHAR(0x00d9))
SELECT @vcResult = REPLACE(@vcResult, ‘Ú’, NCHAR(0x00fa))
SELECT @vcResult = REPLACE(@vcResult, ‘Û’, NCHAR(0x00db))
SELECT @vcResult = REPLACE(@vcResult, ‘Ü’, NCHAR(0x00dc))
SELECT @vcResult = REPLACE(@vcResult, ‘Ý’, NCHAR(0x00dd))
SELECT @vcResult = REPLACE(@vcResult, ‘Þ’, NCHAR(0x00de))
SELECT @vcResult = REPLACE(@vcResult, ‘ß’, NCHAR(0x00df))
SELECT @vcResult = REPLACE(@vcResult, ‘à’, NCHAR(0x00e0))
SELECT @vcResult = REPLACE(@vcResult, ‘á’, NCHAR(0x00e1))
SELECT @vcResult = REPLACE(@vcResult, ‘â’, NCHAR(0x00e2))
SELECT @vcResult = REPLACE(@vcResult, ‘ã’, NCHAR(0x00e3))
SELECT @vcResult = REPLACE(@vcResult, ‘ä’, NCHAR(0x00e4))
SELECT @vcResult = REPLACE(@vcResult, ‘å’, NCHAR(0x00e5))
SELECT @vcResult = REPLACE(@vcResult, ‘æ’, NCHAR(0x00e6))
SELECT @vcResult = REPLACE(@vcResult, ‘ç’, NCHAR(0x00e7))
SELECT @vcResult = REPLACE(@vcResult, ‘è’, NCHAR(0x00e8))
SELECT @vcResult = REPLACE(@vcResult, ‘é’, NCHAR(0x00e9))
SELECT @vcResult = REPLACE(@vcResult, ‘ê’, NCHAR(0x00ea))
SELECT @vcResult = REPLACE(@vcResult, ‘ë’, NCHAR(0x00eb))
SELECT @vcResult = REPLACE(@vcResult, ‘ì’, NCHAR(0x00ec))
SELECT @vcResult = REPLACE(@vcResult, ‘í’, NCHAR(0x00ed))
SELECT @vcResult = REPLACE(@vcResult, ‘î’, NCHAR(0x00ee))
SELECT @vcResult = REPLACE(@vcResult, ‘ï’, NCHAR(0x00ef))
SELECT @vcResult = REPLACE(@vcResult, ‘ð’, NCHAR(0x00f0))
SELECT @vcResult = REPLACE(@vcResult, ‘ñ’, NCHAR(0x00f1))
SELECT @vcResult = REPLACE(@vcResult, ‘ò’, NCHAR(0x00f2))
SELECT @vcResult = REPLACE(@vcResult, ‘ó’, NCHAR(0x00f3))
SELECT @vcResult = REPLACE(@vcResult, ‘ô’, NCHAR(0x00f4))
SELECT @vcResult = REPLACE(@vcResult, ‘õ’, NCHAR(0x00f5))
SELECT @vcResult = REPLACE(@vcResult, ‘ö’, NCHAR(0x00f6))
SELECT @vcResult = REPLACE(@vcResult, ‘÷’, NCHAR(0x00f7))
SELECT @vcResult = REPLACE(@vcResult, ‘ø’, NCHAR(0x00f8))
SELECT @vcResult = REPLACE(@vcResult, ‘ù’, NCHAR(0x00f9))
SELECT @vcResult = REPLACE(@vcResult, ‘ú’, NCHAR(0x00fa))
SELECT @vcResult = REPLACE(@vcResult, ‘û’, NCHAR(0x00fb))
SELECT @vcResult = REPLACE(@vcResult, ‘ü’, NCHAR(0x00fc))
SELECT @vcResult = REPLACE(@vcResult, ‘ý’, NCHAR(0x00fd))
SELECT @vcResult = REPLACE(@vcResult, ‘þ’, NCHAR(0x00fe))
SELECT @vcResult = REPLACE(@vcResult, ‘ÿ’, NCHAR(0x00ff))
SELECT @vcResult = REPLACE(@vcResult, ‘Œ’, NCHAR(0×0152))
SELECT @vcResult = REPLACE(@vcResult, ‘œ’, NCHAR(0×0153))
SELECT @vcResult = REPLACE(@vcResult, ‘Š’, NCHAR(0×0160))
SELECT @vcResult = REPLACE(@vcResult, ‘š’, NCHAR(0×0161))
SELECT @vcResult = REPLACE(@vcResult, ‘Ÿ’, NCHAR(0×0178))
SELECT @vcResult = REPLACE(@vcResult, ‘ƒ’, NCHAR(0×0192))
SELECT @vcResult = REPLACE(@vcResult, ‘ˆ’, NCHAR(0x02c6))
SELECT @vcResult = REPLACE(@vcResult, ‘˜’, NCHAR(0x02dc))
SELECT @vcResult = REPLACE(@vcResult, ‘Α’, NCHAR(0×0391))
SELECT @vcResult = REPLACE(@vcResult, ‘Β’, NCHAR(0×0392))
SELECT @vcResult = REPLACE(@vcResult, ‘Γ’, NCHAR(0×0393))
SELECT @vcResult = REPLACE(@vcResult, ‘Δ’, NCHAR(0×0394))
SELECT @vcResult = REPLACE(@vcResult, ‘Ε’, NCHAR(0×0395))
SELECT @vcResult = REPLACE(@vcResult, ‘Ζ’, NCHAR(0×0396))
SELECT @vcResult = REPLACE(@vcResult, ‘Η’, NCHAR(0×0397))
SELECT @vcResult = REPLACE(@vcResult, ‘Θ’, NCHAR(0×0398))
SELECT @vcResult = REPLACE(@vcResult, ‘Ι’, NCHAR(0×0399))
SELECT @vcResult = REPLACE(@vcResult, ‘Κ’, NCHAR(0x039a))
SELECT @vcResult = REPLACE(@vcResult, ‘Λ’, NCHAR(0x039b))
SELECT @vcResult = REPLACE(@vcResult, ‘Μ’, NCHAR(0x039c))
SELECT @vcResult = REPLACE(@vcResult, ‘Ν’, NCHAR(0x039d))
SELECT @vcResult = REPLACE(@vcResult, ‘Ξ’, NCHAR(0x039e))
SELECT @vcResult = REPLACE(@vcResult, ‘Ο’, NCHAR(0x039f))
SELECT @vcResult = REPLACE(@vcResult, ‘Π’, NCHAR(0x03a0))
SELECT @vcResult = REPLACE(@vcResult, ‘& Rho ;’, NCHAR(0x03a1))
SELECT @vcResult = REPLACE(@vcResult, ‘Σ’, NCHAR(0x03a3))
SELECT @vcResult = REPLACE(@vcResult, ‘Τ’, NCHAR(0x03a4))
SELECT @vcResult = REPLACE(@vcResult, ‘Υ’, NCHAR(0x03a5))
SELECT @vcResult = REPLACE(@vcResult, ‘Φ’, NCHAR(0x03a6))
SELECT @vcResult = REPLACE(@vcResult, ‘Χ’, NCHAR(0x03a7))
SELECT @vcResult = REPLACE(@vcResult, ‘Ψ’, NCHAR(0x03a8))
SELECT @vcResult = REPLACE(@vcResult, ‘Ω’, NCHAR(0x03a9))
SELECT @vcResult = REPLACE(@vcResult, ‘α’, NCHAR(0x03b1))
SELECT @vcResult = REPLACE(@vcResult, ‘β’, NCHAR(0x03b2))
SELECT @vcResult = REPLACE(@vcResult, ‘γ’, NCHAR(0x03b3))
SELECT @vcResult = REPLACE(@vcResult, ‘δ’, NCHAR(0x03b4))
SELECT @vcResult = REPLACE(@vcResult, ‘ε’, NCHAR(0x03b5))
SELECT @vcResult = REPLACE(@vcResult, ‘ζ’, NCHAR(0x03b6))
SELECT @vcResult = REPLACE(@vcResult, ‘η’, NCHAR(0x03b7))
SELECT @vcResult = REPLACE(@vcResult, ‘θ’, NCHAR(0x03b8))
SELECT @vcResult = REPLACE(@vcResult, ‘ι’, NCHAR(0x03b9))
SELECT @vcResult = REPLACE(@vcResult, ‘κ’, NCHAR(0x03ba))
SELECT @vcResult = REPLACE(@vcResult, ‘λ’, NCHAR(0x03bb))
SELECT @vcResult = REPLACE(@vcResult, ‘μ’, NCHAR(0x03bc))
SELECT @vcResult = REPLACE(@vcResult, ‘ν’, NCHAR(0x03bd))
SELECT @vcResult = REPLACE(@vcResult, ‘ξ’, NCHAR(0x03be))
SELECT @vcResult = REPLACE(@vcResult, ‘ο’, NCHAR(0x03bf))
SELECT @vcResult = REPLACE(@vcResult, ‘π’, NCHAR(0x03c0))
SELECT @vcResult = REPLACE(@vcResult, ‘ρ’, NCHAR(0x03c1))
SELECT @vcResult = REPLACE(@vcResult, ‘ς’, NCHAR(0x03c2))
SELECT @vcResult = REPLACE(@vcResult, ‘σ’, NCHAR(0x03c3))
SELECT @vcResult = REPLACE(@vcResult, ‘τ’, NCHAR(0x03c4))
SELECT @vcResult = REPLACE(@vcResult, ‘υ’, NCHAR(0x03c5))
SELECT @vcResult = REPLACE(@vcResult, ‘φ’, NCHAR(0x03c6))
SELECT @vcResult = REPLACE(@vcResult, ‘χ’, NCHAR(0x03c7))
SELECT @vcResult = REPLACE(@vcResult, ‘ψ’, NCHAR(0x03c8))
SELECT @vcResult = REPLACE(@vcResult, ‘ω’, NCHAR(0x03c9))
SELECT @vcResult = REPLACE(@vcResult, ‘ϑ’, NCHAR(0x03d1))
SELECT @vcResult = REPLACE(@vcResult, ‘ϒ’, NCHAR(0x03d2))
SELECT @vcResult = REPLACE(@vcResult, ‘ϖ’, NCHAR(0x03d6))
SELECT @vcResult = REPLACE(@vcResult, ‘ ’, NCHAR(0×2002))
SELECT @vcResult = REPLACE(@vcResult, ‘ ’, NCHAR(0×2003))
SELECT @vcResult = REPLACE(@vcResult, ‘ ’, NCHAR(0×2009))
SELECT @vcResult = REPLACE(@vcResult, ‘‌’, NCHAR(0x200c))
SELECT @vcResult = REPLACE(@vcResult, ‘‍’, NCHAR(0x200d))
SELECT @vcResult = REPLACE(@vcResult, ‘‎’, NCHAR(0x200e))
SELECT @vcResult = REPLACE(@vcResult, ‘‏’, NCHAR(0x200f))
SELECT @vcResult = REPLACE(@vcResult, ‘–’, NCHAR(0×2013))
SELECT @vcResult = REPLACE(@vcResult, ‘—’, NCHAR(0×2014))
SELECT @vcResult = REPLACE(@vcResult, ‘‘’, NCHAR(0×2018))
SELECT @vcResult = REPLACE(@vcResult, ‘’’, NCHAR(0×2019))
SELECT @vcResult = REPLACE(@vcResult, ‘‚’, NCHAR(0x201a))
SELECT @vcResult = REPLACE(@vcResult, ‘“’, NCHAR(0x201c))
SELECT @vcResult = REPLACE(@vcResult, ‘”’, NCHAR(0x201d))
SELECT @vcResult = REPLACE(@vcResult, ‘„’, NCHAR(0x201e))
SELECT @vcResult = REPLACE(@vcResult, ‘†’, NCHAR(0×2020))
SELECT @vcResult = REPLACE(@vcResult, ‘‡’, NCHAR(0×2021))
SELECT @vcResult = REPLACE(@vcResult, ‘•’, NCHAR(0×2022))
SELECT @vcResult = REPLACE(@vcResult, ‘…’, NCHAR(0×2026))
SELECT @vcResult = REPLACE(@vcResult, ‘‰’, NCHAR(0×2030))
SELECT @vcResult = REPLACE(@vcResult, ‘′’, NCHAR(0×2032))
SELECT @vcResult = REPLACE(@vcResult, ‘″’, NCHAR(0×2033))
SELECT @vcResult = REPLACE(@vcResult, ‘‹’, NCHAR(0×2039))
SELECT @vcResult = REPLACE(@vcResult, ‘›’, NCHAR(0x203a))
SELECT @vcResult = REPLACE(@vcResult, ‘‾’, NCHAR(0x203e))
SELECT @vcResult = REPLACE(@vcResult, ‘⁄’, NCHAR(0×2044))
SELECT @vcResult = REPLACE(@vcResult, ‘€’, NCHAR(0x20ac))
SELECT @vcResult = REPLACE(@vcResult, ‘ℑ’, NCHAR(0×2111))
SELECT @vcResult = REPLACE(@vcResult, ‘℘’, NCHAR(0×2118))
SELECT @vcResult = REPLACE(@vcResult, ‘ℜ’, NCHAR(0x211c))
SELECT @vcResult = REPLACE(@vcResult, ‘™’, NCHAR(0×2122))
SELECT @vcResult = REPLACE(@vcResult, ‘ℵ’, NCHAR(0×2135))
SELECT @vcResult = REPLACE(@vcResult, ‘←’, NCHAR(0×2190))
SELECT @vcResult = REPLACE(@vcResult, ‘↑’, NCHAR(0×2191))
SELECT @vcResult = REPLACE(@vcResult, ‘→’, NCHAR(0×2192))
SELECT @vcResult = REPLACE(@vcResult, ‘↓’, NCHAR(0×2193))
SELECT @vcResult = REPLACE(@vcResult, ‘↔’, NCHAR(0×2194))
SELECT @vcResult = REPLACE(@vcResult, ‘↵’, NCHAR(0x21b5))
SELECT @vcResult = REPLACE(@vcResult, ‘⇐’, NCHAR(0x21d0))
SELECT @vcResult = REPLACE(@vcResult, ‘⇑’, NCHAR(0x21d1))
SELECT @vcResult = REPLACE(@vcResult, ‘⇒’, NCHAR(0x21d2))
SELECT @vcResult = REPLACE(@vcResult, ‘⇓’, NCHAR(0x21d3))
SELECT @vcResult = REPLACE(@vcResult, ‘⇔’, NCHAR(0x21d4))
SELECT @vcResult = REPLACE(@vcResult, ‘∀’, NCHAR(0×2200))
SELECT @vcResult = REPLACE(@vcResult, ‘∂’, NCHAR(0×2202))
SELECT @vcResult = REPLACE(@vcResult, ‘∃’, NCHAR(0×2203))
SELECT @vcResult = REPLACE(@vcResult, ‘∅’, NCHAR(0×2205))
SELECT @vcResult = REPLACE(@vcResult, ‘∇’, NCHAR(0×2207))
SELECT @vcResult = REPLACE(@vcResult, ‘∈’, NCHAR(0×2208))
SELECT @vcResult = REPLACE(@vcResult, ‘∉’, NCHAR(0×2209))
SELECT @vcResult = REPLACE(@vcResult, ‘∋’, NCHAR(0x220b))
SELECT @vcResult = REPLACE(@vcResult, ‘∏’, NCHAR(0x220f))
SELECT @vcResult = REPLACE(@vcResult, ‘∑’, NCHAR(0×2211))
SELECT @vcResult = REPLACE(@vcResult, ‘−’, NCHAR(0×2212))
SELECT @vcResult = REPLACE(@vcResult, ‘∗’, NCHAR(0×2217))
SELECT @vcResult = REPLACE(@vcResult, ‘√’, NCHAR(0x221a))
SELECT @vcResult = REPLACE(@vcResult, ‘∝’, NCHAR(0x221d))
SELECT @vcResult = REPLACE(@vcResult, ‘∞’, NCHAR(0x221e))
SELECT @vcResult = REPLACE(@vcResult, ‘∠’, NCHAR(0×2220))
SELECT @vcResult = REPLACE(@vcResult, ‘∧’, NCHAR(0×2227))
SELECT @vcResult = REPLACE(@vcResult, ‘∨’, NCHAR(0×2228))
SELECT @vcResult = REPLACE(@vcResult, ‘∩’, NCHAR(0×2229))
SELECT @vcResult = REPLACE(@vcResult, ‘∪’, NCHAR(0x222a))
SELECT @vcResult = REPLACE(@vcResult, ‘∫’, NCHAR(0x222b))
SELECT @vcResult = REPLACE(@vcResult, ‘&there4;’, NCHAR(0×2234))
SELECT @vcResult = REPLACE(@vcResult, ‘∼’, NCHAR(0x223c))
SELECT @vcResult = REPLACE(@vcResult, ‘≅’, NCHAR(0×2245))
SELECT @vcResult = REPLACE(@vcResult, ‘≈’, NCHAR(0×2248))
SELECT @vcResult = REPLACE(@vcResult, ‘≠’, NCHAR(0×2260))
SELECT @vcResult = REPLACE(@vcResult, ‘≡’, NCHAR(0×2261))
SELECT @vcResult = REPLACE(@vcResult, ‘≤’, NCHAR(0×2264))
SELECT @vcResult = REPLACE(@vcResult, ‘≥’, NCHAR(0×2265))
SELECT @vcResult = REPLACE(@vcResult, ‘⊂’, NCHAR(0×2282))
SELECT @vcResult = REPLACE(@vcResult, ‘⊃’, NCHAR(0×2283))
SELECT @vcResult = REPLACE(@vcResult, ‘⊄’, NCHAR(0×2284))
SELECT @vcResult = REPLACE(@vcResult, ‘⊆’, NCHAR(0×2286))
SELECT @vcResult = REPLACE(@vcResult, ‘⊇’, NCHAR(0×2287))
SELECT @vcResult = REPLACE(@vcResult, ‘⊕’, NCHAR(0×2295))
SELECT @vcResult = REPLACE(@vcResult, ‘⊗’, NCHAR(0×2297))
SELECT @vcResult = REPLACE(@vcResult, ‘⊥’, NCHAR(0x22a5))
SELECT @vcResult = REPLACE(@vcResult, ‘⋅’, NCHAR(0x22c5))
SELECT @vcResult = REPLACE(@vcResult, ‘⌈’, NCHAR(0×2308))
SELECT @vcResult = REPLACE(@vcResult, ‘⌉’, NCHAR(0×2309))
SELECT @vcResult = REPLACE(@vcResult, ‘⌊’, NCHAR(0x230a))
SELECT @vcResult = REPLACE(@vcResult, ‘⌋’, NCHAR(0x230b))
SELECT @vcResult = REPLACE(@vcResult, ‘⟨’, NCHAR(0×2329))
SELECT @vcResult = REPLACE(@vcResult, ‘⟩’, NCHAR(0x232a))
SELECT @vcResult = REPLACE(@vcResult, ‘◊’, NCHAR(0x25ca))
SELECT @vcResult = REPLACE(@vcResult, ‘♠’, NCHAR(0×2660))
SELECT @vcResult = REPLACE(@vcResult, ‘♣’, NCHAR(0×2663))
SELECT @vcResult = REPLACE(@vcResult, ‘♥’, NCHAR(0×2665))
SELECT @vcResult = REPLACE(@vcResult, ‘♦’, NCHAR(0×2666))

SELECT @vcResult = REPLACE(@vcResult, ”, @vcCrLf)

RETURN @vcResult
END
- on November 15, 2011 at 3:04 am | Reply alex
  
  this is not working properly. Can you help? I would like to get this to work for me…seems there are missing chars where you use CHARINDEX (see code under /*tag general */ )…see code above…
- on November 30, 2011 at 8:50 pm | Reply Alex
  
  These lines of code above seem to be incomplete. Can someone help me fix this?
  
  SET @Start = CHARINDEX(”,@HTMLText,CHARINDEX(‘ 0
  AND @End > 0
  AND @Length > 0
  BEGIN
  SET @HTMLText = STUFF(@HTMLText,@Start,@Length,”)
  SET @Start = CHARINDEX(”,@HTMLText,CHARINDEX(‘ 0)
on October 25, 2011 at 2:47 pm | Reply Ryan Daulton

How can I alter this proc to give it a table parameter and table output?
- on November 3, 2011 at 3:19 pm | Reply madhivanan
  
  Have a look at Table-valued-parameters in SQL Server helpf file for more informations
on November 30, 2011 at 4:30 am | Reply Peter Brannan

Awesome, works well thanx mate
on January 20, 2012 at 10:03 pm | Reply Christopher Wolff

Sorry so late to the party but I just wanted to add one part that I put in to make it perfect.

RETURN LTRIM(RTRIM(REPLACE(@HTMLText,’ ’,’ ‘)))

Thanks for your help on this and all other articles I have used
- on January 20, 2012 at 10:05 pm | Reply Christopher Wolff
  
  Oops it took out my HTML tag. We meant to have “ ” in the middle.
on January 23, 2012 at 11:56 am | Reply Saeed Neamati

Though this article has been written in year 2007, I found it really useful, in 2012. I love the web for that reason. I only didn’t understand why should we replace single quotes with 2 single quotes? What the reason is?
- on January 23, 2012 at 10:38 pm | Reply Alex
  
  Just out of curiousity….do you have the same as I do in the code you are using as I am ? I copied and pasted from abofe and I have this code below and seems incomplete/cutoff and It is not working for m.
  
  SET @Start = CHARINDEX(”,@HTMLText,CHARINDEX(‘ 0
  AND @End > 0
  AND @Length > 0
  BEGIN
  SET @HTMLText = STUFF(@HTMLText,@Start,@Length,”)
  SET @Start = CHARINDEX(”,@HTMLText,CHARINDEX(‘ 0)
on January 23, 2012 at 10:37 pm | Reply Alex

Does anyone know why these lines above are incomplete. Doesn’t work for me….

These lines of code above seem to be incomplete. Can someone help me fix this?

SET @Start = CHARINDEX(”,@HTMLText,CHARINDEX(‘ 0
AND @End > 0
AND @Length > 0
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,”)
SET @Start = CHARINDEX(”,@HTMLText,CHARINDEX(‘ 0)
on May 25, 2012 at 2:42 pm | Reply chandan varma

how we can the write the prime number program in t-sql using sql server 2005,there are some conditions:-
1:-we don’t use table,
2:-use if-else and while loop
on June 11, 2012 at 5:36 pm | Reply Amaan

hi pinal ,
i want to open html file in sql
so please give me solution
on August 15, 2012 at 7:12 pm | Reply Jit

Hi Pinal Sir,
The function is very useful
But I am trying to get string in bold if Tag is specified using TSQL commands.
Same for other tags also.
Plz give me soln !!!!!!!!!!!!
on September 1, 2012 at 7:04 am | Reply jcoreortiz

GRACIAS,,,ESTA SENCILLO LA FUNCION.
on November 1, 2012 at 12:16 pm | Reply कौशल उप्रेती

case:if the HTML tag is like HTML BODY , is this method useful for this case . if not please suggests.