The JSON RFC states that:
All Unicode characters may be placed within the quotation marks except for the characters that must be escaped: quotation mark, reverse solidus, and the control characters (U+0000 through U+001F).As it turns out, the way of iterating through a string in T-SQL is as such:
set @wcount = 0 set @index = 1 set @len = len(@string) while @index <= @len begin set @char = substring(@string, @index, 1) /* do something with @char */ set @index += 1 endTo escape a quote or a backslash, we just prefix it with a backslash. As for the control characters, this gets a bit trickier, as we need to convert them to \u-notation that is used in JSON. We can use the built-in unicode function to get the ordinal value of a char and determine that it needs to be escaped.
when unicode(@char) < 32Then we take advantage of the fn_varbintohexstr system function to convert a char value through varbinary type to a hex string.
sys.fn_varbintohexstr(cast(@char as varbinary))Finally, after some string chopping and concatenating, we get what we want:
'\u00' + right(sys.fn_varbintohexstr(cast(@char as varbinary)), 2)Here's the code of the function json_escape in its entirety.
if object_id(N'dbo.json_escape', N'FN') is not null drop function dbo.json_escape go create function dbo.json_escape (@string varchar(max)) returns varchar(max) as begin declare @wcount int, @index int, @len int, @char char, @escaped_string varchar(max) set @escaped_string = '' set @wcount = 0 set @index = 1 set @len = len(@string) while @index <= @len begin set @char = substring(@string, @index, 1) set @escaped_string += case when @char = '"' then '\"' when @char = '\' then '\\' when unicode(@char) < 32 then '\u00' + right(sys.fn_varbintohexstr(cast(@char as varbinary)), 2) else @char end set @index += 1 end return(@escaped_string) end go
No comments:
Post a Comment