Apostrophe in text creates where clause error

Hi there,
I am having an apostrophe in the text of the where clause which is causing an error in the where clause.

Specifically, the error message is:

{
  "code": 1017,
  "errorData": {},
  "message": "Where clause 'tutor_name = 'La'OshXX ReXX'' is invalid"
} 

The logic is as follows:

It works for most items in the DB but one tutor_name has an apostrophe (La'OshXX ReXX) and this causes the error I think.

Any thoughts on how to overcome this issue?

Application ID: 4A47197B-AE30-FA84-FF56-0071F4010900
Method: tutorAdd

Hello @Andreas_Marinopoulos,

Please escape the apostrophe using the backslash character.

Regards,
Olha

Hi @olhadanylova, can you kindly elaborate as I am not sure how to implement your suggestion?

The actual entry in the DB contains the apostrophe. Then the name is brought into the variable, when I use the Get property block.

If I only have one entry, I could do something like La\'OshXX ReXX.

However, as this is a variable, there may be other entries that appear later with special characters. Is there a way to deal with those?

Please try with Regex blocks:

Regards,
Olha

1 Like

Thanks, will try that!

I am getting an empty list returned unfortunately. I would love to troubleshoot this myself but cannot find any docs on the RegExp blocks. Is there a video or blog post somewhere?

Alternatively, could you explain what you are doing with the g and " entries? If I understand, I might be able to tinker with it.

In case you want to see my real test:
Application ID: 4A47197B-AE30-FA84-FF56-0071F4010900
Method: test

Hello @Andreas_Marinopoulos

In the Replace block, in the replacement field, you use quotation marks ", but you need to use two apostrophes '', replace and it should work.
Documentation for this - Search with the Where Clause:

If the value itself has a single quote, it can be ā€œescapedā€ by adding an additional single quote. For example:
name = ā€˜Charlieā€™ā€˜s Angelsā€™

Also, in one of your examples I saw the line - tutor_name = 'La'OshXX ReXX', if by XX you mean any character, then you will need to replace XX with %, a description of this can also be found on the documentation page that I wrote above under - the name substring literal, also if you want to use substring literal you need to replace the = sign with a like, you get tutor_name like 'La''Osh% Re%'.

Regarding the work of the Replace block:
original text - the text to be converted
pattern/regex - which characters will be replaced in the original text
replacement - a character/symbols to which all occurrences from the pattern will be replaced
In your case, all characters ' in the original string La'Oshiaa Reed will be replaced with '', and you get La''Oshiaa Reed.

1 Like

Works perfectly, thank you both.