Using ESCAPESINGLEQUOTE function

The ESCAPESINGLEQUOTE function helps you to ignore the single quotes that are typically present in strings where a single quote or an apostrophe is an integral part of the data. For example in an Author table in a MySQL there can be Names like O’Reilly that would have a single quote as an integral part. When you are inserting the name using an insert query then it may lead to some errors. In such cases, the ESCAPESINGLEQUOTE would allow the single quote symbol to be ignored and used as a part of the string and not a special character.

Syntax

ESCAPESINGLEQUOTE(string / json field)

Where,

  • <string/json>: is the string to be used

Now let us take an example to insert data into a MySQL database from a Product details form. Say you are adding the Product name, which may contain single quote data or which may contain the apostrophe symbol.

In such cases, we can use the compute block and add the respective columns to ignore the single quote from the data and use it as a part of the string using the ESCAPESINGLEQUOTE function.

Considering that we are adding a product name ‘ Hershey’s chocolate syrup ‘ which contains an “ apostrophe s ”, when inserting into the database it will be taken as [{“productname2”:“Hershey’s chocolate syrup”,“price”:20}] which may cause a problem in the insert statement due to the single quote which interferes with the query statement. So here in a Compute block if you add the function to ignore the single quote as

ESCAPESINGLEQUOTE( formrepeat)

the output would be

[{“productname2”:“Hershey’s chocolate syrup”,“price”:20}]

which would be a valid format to insert to the MySQL database or any SQL in use.

Thus you can make use of the ESCAPESINGLEQUOTE() function that would enable you to ignore the single quote as a part of the code.