Skip to content

Instantly share code, notes, and snippets.

@ChicBrother
Forked from chilismaug/JSONEscaped.sql
Created September 16, 2021 14:50
Show Gist options
  • Save ChicBrother/ba4d77e699a6b53e9b12815304379411 to your computer and use it in GitHub Desktop.
Save ChicBrother/ba4d77e699a6b53e9b12815304379411 to your computer and use it in GitHub Desktop.
Phil Factor utility function to clean the json strings
IF OBJECT_ID (N'dbo.JSONEscaped') IS NOT NULL DROP FUNCTION dbo.JSONEscaped
GO
CREATE FUNCTION [dbo].[JSONEscaped] ( /* this is a simple utility function that takes a SQL String with all its clobber and outputs it as a sting with all the JSON escape sequences in it.*/
@Unescaped NVARCHAR(MAX) --a string with maybe characters that will break json
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SELECT @Unescaped = REPLACE(@Unescaped, FROMString, TOString)
FROM (SELECT '' AS FromString, '\' AS ToString
UNION ALL SELECT '"', '"'
UNION ALL SELECT '/', '/'
UNION ALL SELECT CHAR(08),'b'
UNION ALL SELECT CHAR(12),'f'
UNION ALL SELECT CHAR(10),'n'
UNION ALL SELECT CHAR(13),'r'
UNION ALL SELECT CHAR(09),'t'
) substitutions
RETURN @Unescaped
END
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment