0

I can't find a proper way to escape apostrophe sign(’) in my mysql query. Regexp I have, works fine with online tools for regexp testing.

Problematic example is the string G’Schlössl.

I want to have optional apostrophe sign in the query in front of the s character G(’?)Schlö(’?)ssl for all the different cases which could occur in other strings. I am not sure if the problem is caused by incorrect sign escaping but I have tried many options like ’?, \’?, \’{0,1} which works for the first occurrence but doesn't for the second optional one and cause query to return nothing. Other possibilities like ’’?, [’]?, [\’]?, [\’]{0,1} does not work even for the first one.

select id, name from restaurant where name regexp '.*g\’?(s|ß|ss|sz)chl(o|ö|oe)\’?s.*';

When I remove the last \’? it works:

select id, name from restaurant where name regexp '.*g\’?(s|ß|ss|sz)chl(o|ö|oe)s.*';

When I replace the last \’? with x? it works as well:

select id, name from restaurant where name regexp '.*g\’?(s|ß|ss|sz)chl(o|ö|oe)x?s.*';

Any ideas where the problem is or what else to try?

This thread explains escaping normal single quote only, which seems not to work in my case.

Community
  • 1
  • 1
rhorvath
  • 3,525
  • 2
  • 23
  • 30
  • If your first `\’` works, so should do the second one at the end. What do you want to match with this pattern? – Yasen Zhelev Oct 19 '15 at 10:40
  • Have you tried using [[.apostrophe.]]? – PaulF Oct 19 '15 at 10:41
  • @YasenZhelev yea i think it should work and I have no idea why it doesn't in mysql and does in js. I want a query for the cases like Jimmy's and someone typing just jimmys to a search. Idea was to include ’? in front of s char. – rhorvath Oct 19 '15 at 11:00
  • @PaulF yea, doesn't work even for the first one. But I wonder why even [’]? does not work as well. – rhorvath Oct 19 '15 at 11:01
  • 1
    Have a look at these two links :http://stackoverflow.com/questions/28286059/why-is-ascii-representation-of-this-character-returns-as-226-128-153 AND http://stackoverflow.com/questions/7067566/unicode-in-mysql-regex - first shows the "apostrophe" is actually a Unicode character, second shows REGEX doesn't work well with Unicode. – PaulF Oct 19 '15 at 11:02
  • 1
    First, check what's in the database exactly, is it a `'` or a `’`, if it's a `'` then `''?` will work, if it's a `’` then `’?` will work, if it's a `’` and `’?` does not work then you have a mismatch between the encoding in your database and the encoding in your query – Mystic Odin Oct 19 '15 at 11:06

1 Answers1

0

Instead of \’?, try (’)?. I'm thinking that the ? may apply to only the last byte of ’. By using parentheses instead, the ? applies to the entire 3 bytes (hex E28099) of the "RIGHT SINGLE QUOTATION MARK".

Rick James
  • 135,179
  • 13
  • 127
  • 222