Convert unicode to Chinese char inside JSON?

Question

I've some data in database (sql server 2008) in JSON format with unicode like this:

{"PCode":"999999","Code":"123","effect":"\u4e2d\u6587\u5b57\u6e2c\u8a66"}

\u4e2d\u6587\u5b57\u6e2c\u8a66 are Chinese characters, how can I retieve the data in Chinese characters?

Answer 1

Answer:

It seems you use SQL Server 2008 (where JSON support is not available), so one possible aproach is to parse the unicode sequence as XML. Note, that XML doesn't use \u notation, so you need to transform this JSON text using XML character reference &#x[0-9a-fA-F];:

T-SQL:

DECLARE @text nvarchar(max)
SET @text = N'\u4e2d\u6587\u5b57\u6e2c\u8a66'

DECLARE @xml xml
SET @xml = N'<x>' + STUFF(REPLACE(@text, N'\u', N';&#x'), 1, 1, N'') + N';</x>'
SELECT @xml.value('(/x)[1]', 'nvarchar(max)') AS [ChineseText]

Result:

ChineseText
-----------
中文字測試

Note, that from SQL Server 2016, you can easily parse the JSON using JSON_VALUE().

SELECT JSON_VALUE(CONCAT(N'["', @text, N'"]'), '$[0]') AS [ChineseText]

Update:

If the JSON has additional characters, you need to split the JSON content using XML and \u as separator:

DECLARE @text nvarchar(max)
SET @text = N'\u4e2deng\u6587eng\u5b57234\u6e2c\u8a66'

DECLARE @xml xml
SET @xml = CONCAT(STUFF(REPLACE(@text, N'\u', N'</x><x>'), 1, 4, N''), N'</x>')
SELECT 
   CONVERT(xml, N'<x>&#x' + LEFT(t.value('.', 'nvarchar(max)'), 4) + N';</x>').value('(/x)[1]', 'nvarchar(max)'), 
   STUFF(t.value('.', 'nvarchar(max)'), 1, 4, N'')
FROM @xml.nodes('/x') AS x(t)
FOR XML PATH('')

Result:

中eng文eng字234測試

Answer 2

Any better practice?

DECLARE @Text NVARCHAR(MAX)
DECLARE @stringToSplit NVARCHAR(MAX)
DECLARE @returnList TABLE ([Name] [nvarchar] (500))

set @stringToSplit = '\u4e2d\u6587\u5b57\u6e2c\u8a66'

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX('\u', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX('\u', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  IF @name <> ''
  BEGIN
  INSERT INTO @returnList 
  SELECT NCHAR( CONVERT(INT, CONVERT(VARBINARY,@name, 2) ) )
  END

  SELECT @stringToSplit = SUBSTRING(SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos),2,LEN(@stringToSplit))
 END

 INSERT INTO @returnList
 SELECT NCHAR( CONVERT(INT, CONVERT(VARBINARY,@stringToSplit, 2) ) )

 select * from @returnList