Skip to content

Instantly share code, notes, and snippets.

@chilismaug

chilismaug/JSONEscaped.sql

Created April 14, 2020 12:10
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save chilismaug/2ef6fd2d1d29a94328398874e2edf95a to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save chilismaug/2ef6fd2d1d29a94328398874e2edf95a to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. chilismaug created this gist Apr 14, 2020.
    22 changes: 22 additions & 0 deletions JSONEscaped.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,22 @@
    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