I am trying to convert the HTML names like & " etc to their equivalent CHAR values using the SQL below. I was testing this in SQL Server 2012.
Test 1 (This works fine):
GO
DECLARE @inputString VARCHAR(MAX)= '&testString&'
DECLARE @codePos INT, @codeEncoded VARCHAR(7), @startIndex INT, @resultString varchar(max)
SET @resultString = LTRIM(RTRIM(@inputString))
SELECT @startIndex = PATINDEX('%&%', @resultString)
WHILE @startIndex > 0
BEGIN
SELECT @resultString = REPLACE(@resultString, '&', '&'), @startIndex=PATINDEX('%&%', @resultString)
END
PRINT @resultString
Go
Output:
&testString&
Test 2 (this isn't worked): Since the above worked, I have tried to extend this to deal with more characters as following:
DECLARE @htmlNames TABLE (ID INT IDENTITY(1,1), asciiDecimal INT, htmlName varchar(50))
INSERT INTO @htmlNames
VALUES (34,'"'),(38,'&'),(60,'<'),(62,'>'),(160,' '),(161,'¡'),(162,'¢')
-- I would load the full list of HTML names into this TABLE varaible, but removed for testing purposes
DECLARE @inputString VARCHAR(MAX)= '&testString&'
DECLARE @count INT = 0
DECLARE @id INT = 1
DECLARE @charCode INT, @htmlName VARCHAR(30)
DECLARE @codePos INT, @codeEncoded VARCHAR(7), @startIndex INT
, @resultString varchar(max)
SELECT @count=COUNT(*) FROM @htmlNames
WHILE @id <=@count
BEGIN
SELECT @charCode = asciiDecimal, @htmlname = htmlName
FROM @htmlNames
WHERE ID = @id
SET @resultString = LTRIM(RTRIM(@inputString))
SELECT @startIndex = PATINDEX('%' + @htmlName + '%', @resultString)
While @startIndex > 0
BEGIN
--PRINT @resultString + '|' + @htmlName + '|' + NCHAR(@charCode)
SELECT @resultString = REPLACE(@resultString, @htmlName, NCHAR(@charCode))
SET @startIndex=PATINDEX('%' + @htmlName + '%', @resultString)
END
SET @id=@id + 1
END
PRINT @resultString
GO
Output:
&testString&
I cannot figure out where I'm going wrong? Any help would be much appreciated.
I am not interested to load the string values into application layer and then apply HTMLDecode and save back to the database.
EDIT:
This line SET @resultString = LTRIM(RTRIM(@inputString)) was inside the WHILE so I was overwriting the result with @inputString. Thank you, YanireRomero.
I like @RichardDeeming's solution too, but it didn't suit my needs in this case.
