CODEWITHSUNDEEP
sql-serverjsont-sql
Jonathan Irwin
Jonathan Irwin

Reputation: 5767

MSSQL function to convert string to valid JSON

I would like to create a MSSQL scalar function to convert a string to valid JSON. As far as I can tell newline, returns and quote characters are an issue but I cannot find a complete list or a complete function.

This is what I have so far:

Create Function [dbo].[StringToJSON] 
(@MyString as varchar(Max))
Returns varchar(Max)
As
Begin
    --NULL
    Set @MyString = Replace(@MyString,CHAR(0),' ');
    --Horizontal Tab
    Set @MyString = Replace(@MyString,CHAR(9),' ');
    --Line Feed
    Set @MyString = Replace(@MyString,CHAR(10),'\n');
    --Vertical Tab
    Set @MyString = Replace(@MyString,CHAR(11),' ');
    --Form Feed
    Set @MyString = Replace(@MyString,CHAR(12),'\n');
    --Carriage Return
    Set @MyString = Replace(@MyString,CHAR(13),'\n');
    --Column Break
    Set @MyString = Replace(@MyString,CHAR(14),' ');
    --Non-breaking space
    Set @MyString = Replace(@MyString,CHAR(160),'');

    Set @MyString = LTRIM(RTRIM(@MyString));
    Return @MyString
End
Go

Upvotes: 2

Views: 1213

Answers (1)

Whirl Mind
Whirl Mind

Reputation: 884

This link mentions other characters :

msdn.microsoft.com/en-us/library/dn921889.aspx

Upvotes: 1

Related Questions