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