CODEWITHSUNDEEP
jsont-sql
Flimzy_Programmer
Flimzy_Programmer

Reputation: 543

Avoid escaping characters when converting from tabular data to json

I have some problems converting tabular data to JSON using the FOR JSON PATH syntax:

If i do a standard query:

SELECT b.Name FROM dbo

I get results of the form: 12/5-A-1. I need this converted to JSON data without escaping the backslash character. However, when i convert it to JSON:

SELECT b.Name FROM dbo FOR JSON PATH, WITHOUT ARRAY_WRAPPER

the result is of the form: {"Name": "12\/5-A-1"}

How can i do this transformation without escaping the backslash character and get the result {"Name": "12/5-A-1"}?

Upvotes: 0

Views: 362

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

One option is to use a common table expression to generate the json, and then simply use replace when selecting from the common table expression.

First, create and populate sample data (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    [Name] nvarchar(10)
)

INSERT INTO @T ([Name]) VALUES ('12/5-A-1');

The cte:

WITH CTE(Escaped) AS
(

    SELECT [Name]
    FROM @T
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)

The final select:

SELECT REPLACE(Escaped, '\/','/') As Result
FROM CTE

Result:

{"Name":"12/5-A-1"}

Upvotes: 1

Related Questions