STRING_ESCAPE json forward slash

Question

Using the STRING_ESCAPE function found here I am escaping certain columns with string values so that they fit in a JSON format. However when it is applied to URLs it behaves, in my opinion, strangely.

SELECT STRING_ESCAPE('https://www.website.com/page', 'json')

returns https:\/\/www.website.com\/page.

I can understand that indeed according to this post forward slashes are allowed, not required in JSON and therefore they are included when using this function. But if you create an HTML tag with this value (https:\/\/www.website.com\/page) the link no longer works as, my browser at least, is trying to surf to https://www.website.com//page.

Since I don't know if my original string contains \/ I can't just use REPLACE(STRING_ESCAPE([column], 'json'), '\/', '/') to solve this.

Is there an option to disable the escaping of forward slashes? Or any other clever solution to this problem?

Answer 1

Preamble: The original problem is attempting to JSON-escaped value that is not later consumed as JSON as URLs are not JSON strings. However, this answer does not focus on that incorrect domain usage and/or failure to decode the JSON value before the usage in HTML.

---

Rather, this answer corrects this misbelief:

Since I don't know if my original string contains [\/] I can't just use a replace to solve this.

As long as STRING_ESCAPE is used, it is a valid approach to perform the replacement after using STRING_ESCAPE, as shown in the original question:

REPLACE(STRING_ESCAPE([column], 'json'), '\/', '/')

This is because STRING_ESCAPE escapes every / and \, meaning that any \/ in the original source is also escaped as \\\/. (It would only be problematic if \ was not also escaped.)

Consider these examples, which result in a valid JSON string content without the escaped solidus, that demonstrate the correctness of the simple REPLACE approach.

INPUT             STRING_ESCAPE       REPLACE(.., '\/', '/')
hello\world       hello\\world        hello\\world
hello/world       hello\/world        hello/world
hello\/workd      hello\\\/world      hello\\/world
\\/\/\\           \\\\\/\\\/\\\\      \\\\/\\/\\\\

The values above represent the actual string content.