Category: How do I do X?
Updated

This solution is summarized from an archived support forum post. This information may have changed. If you notice an error, please let us know in Discord.

Replace ’ From Json String

Issue

I am having an issue using JSON data in an MSSQL query. The JSON data contains single quotes that need to be replaced with double single quotes to avoid syntax errors in the query. I tried using the replace() function in JavaScript, but it resulted in an error. Ultimately, with the help of Amelia, I successfully used JSON.parse() and JSON.stringify() to replace the single quotes.

Resolution

Cause

JSON data with single quotes can cause syntax errors in MSSQL queries because single quotes in JSON need to be escaped as double single quotes ("''") in SQL queries.

Solution

If you encounter syntax errors due to single quotes in JSON data, follow these steps to resolve the issue:

  • Convert the JSON data to a string using JSON.stringify().
  • Replace single quotes with escaped single quotes using replaceAll(). Ensure that all occurrences of single quotes are replaced.
  • Parse the modified string back to JSON format using JSON.parse().

The final code to achieve this looks like:

{{JSON.parse(JSON.stringify(data).replaceAll("'", "''"))}}

This approach will resolve syntax errors in MSSQL queries caused by single quotes in JSON data.