STRING_ESCAPE( 'SOME TEXT' , 'json') alternative for SQL Server 2012

Question

We have multiple version of SQL Server (2016, 2012), Unfortunately, STRING_ESCAPE is only working in 2016 as it was introduced in that version. I am getting following error while I execute the same query in SQL Server 2012.

SELECT STRING_ESCAPE(description, 'json') 
FROM SomeTable

Error:

Msg 195, Level 15, State 10, Line 1
'STRING_ESCAPE' is not a recognized built-in function name.

Is there any alternative to STRING_ESCAPE for SQL Server 2012?

Answer 1

No directly built-in function, but you could quite easily create your own function, where you have a table with characters to replace, and their encoding. You could even add a column for json, xml, whatever. The function would then take the data to replace, do a REPLACE and return the "altered" string:

CREATE TABLE dbo.tb_StringEncoding
(
  StringToReplace nvarchar(10),
  StringReplacement nvarchar(10),
  EncodingType nvarchar(25)
  CONSTRAINT pk_StringEncoding PRIMARY KEY
  (EncodingType, StringToReplace)
)

INSERT INTO dbo.tb_StringEncoding(StringToReplace, StringReplacement, EncodingType)
VALUES   ( '"', '\"', 'json')
       , ('\', '\\', 'json')
       , ('/', '\/', 'json')
       , ('{', '\{', 'json') 
       , ('}', '\}', 'json');
GO

CREATE FUNCTION dbo.fn_String_Escape(@StringToEscape nvarchar(max), @Encoding nvarchar(10))
RETURNS nvarchar(max)
BEGIN
  DECLARE @s nvarchar(max);
  SELECT @StringToEscape = REPLACE(@StringToEscape, StringToReplace, StringReplacement)
  FROM dbo.tb_StringEncoding
  WHERE EncodingType = @Encoding;
   RETURN @StringToEscape
END
GO

-- an example how to use it:

DECLARE @description nvarchar(256) = 'Hello \ What are you / waiting for'
SELECT dbo.fn_String_Escape(@description, 'json')

The above is just an example, you'd probably have to include control characters etc., in your table above and then in the function loop through the string to replace - but at least this is a start.