Search code examples
sqlsql-serverasciinon-ascii-charactersextended-ascii

SQL Server numerical reference column decode

Is there a way to decode all the characters with numerical reference in SQL (ex. , 'solu&#231 ;&#227 ;o' -> solução , "&#26696 ; -> 案" ) from a column ?

The space before ";" is not the issue , just added so it would not be embedded .

What i found so far :

  • use htmldecode C# (not an option)
  • use replace for every value (only possible if someone took the time to actually add every char )
Solution

  • If the field is for—or its data is and always will be compatible with—XML, convert it to the XML type and then take the text value:

    SELECT N'🚲', CONVERT(NVARCHAR(MAX), CONVERT(XML, N'🚲'));

    Otherwise, one way is to create a SQL CLR function to use .NET's HTML libraries. (If you have something against C#, there are other .NET languages.)

    And, depending on your version of SQL Server and operating system, there are R libraries and Python libraries that can be made accessible to T-SQL.