Json escape unicode in SQL Server

Question

I got JSon string with escape unicode symbols

\u041e\u043f\u043e\u0440\u0430 \u0448\u0430\u0440\u043e\u0432\u0430\u044f VW GOLF

I know that 4 digits after \u are the hex code of unicode character.

So here's how I decoded those strings

ALTER FUNCTION dbo.Json_Unicode_Decode(@escapedString VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS 
BEGIN
    DECLARE @pos INT = 0,
            @char CHAR,
            @escapeLen TINYINT = 2,
            @hexDigits TINYINT = 4

    SET @pos = CHARINDEX('\u', @escapedString, @pos)

    WHILE @pos > 0
    BEGIN
        SET @char =  NCHAR(CONVERT(varbinary(8), '0x' + SUBSTRING(@escapedString, @pos + @escapeLen, @hexDigits), 1))
        SET @escapedString = STUFF(@escapedString, @pos, @escapeLen + @hexDigits, @char)
        SET @pos = CHARINDEX('\u', @escapedString, @pos)
    END

    RETURN @escapedString
END

Are there more delicate ways to decode them?

Json escape unicode in SQL Server

0 Answers