SQL Server has a pretty good support for JSON. If there is a special character in JSON, then it will throw this error;
JSON path is not properly formatted. Unexpected character ‘#’ is found at position 2
To produce this, here is an example
DECLARE @data NVARCHAR(50)='{"#Name":"Shahzad"}'
-- Print the current JSON
PRINT @data
-- Rename the key (by copying the value to a new key, then deleting the old one)
SET @data=
JSON_MODIFY(
JSON_MODIFY(@data,'$.Contractor', JSON_VALUE(@data,'$.#Name')),
'$.#Name',
NULL
)
-- Print the new JSON
PRINT @dataI didn’t find any built-in support to handle these characters. The workaround I found is to simply replace special characters. Here is how;
DECLARE @data NVARCHAR(50)='{"#Name":"Shahzad"}'
DECLARE @cleanData NVARCHAR(50) = REPLACE(@data, '#Name', 'Name');
-- Print the current JSON
PRINT @data
-- Rename the key (by copying the value to a new key, then deleting the old one)
SET @cleanData=
JSON_MODIFY(
JSON_MODIFY(@cleanData,'$.Contractor', JSON_VALUE(@cleanData,'$.Name')),
'$.Name',
NULL
)
-- Print the new JSON
PRINT @cleanDataAnd the output is;
{“#Name”:”Shahzad”}
{“Contractor”:”Shahzad”}
References