MySQL Forums
Forum List  »  Newbie

Help with escaping in SELECT statement
Posted by: Rick Noel
Date: April 18, 2020 12:03PM

I have a table of strings that represent a comma-separated list. Any quotes in the value are escaped with a backslash. For example, the string "Thor's Hammer" would be stored in the table as "Thor\'s Hammer".

I am trying to construct a SELECT statement to match this value using:

SELECT * FROM table WHERE stringValue LIKE 'Thor\\''s Hammer';

The SELECT statement returns an empty set. How do I construct the WHERE clause to match the backslash and the single quote/apostrophe?

Here is a my table definition with results of various attempts:

mysql> CREATE TABLE data (stringValue VARCHAR(1024) NOT NULL);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO data VALUES('Thor\\''s Hammer');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM data;
+----------------+
| stringValue |
+----------------+
| Thor\'s Hammer |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT * from data WHERE stringValue LIKE 'Thor\\''s Hammer';
Empty set (0.00 sec)

mysql> SELECT * FROM data WHERE stringValue LIKE "Thor\\''s Hammer";
Empty set (0.00 sec)

mysql> SELECT * FROM data WHERE stringValue LIKE "Thor\\'s Hammer";
Empty set (0.00 sec)

Options: ReplyQuote


Subject
Written By
Posted
Help with escaping in SELECT statement
April 18, 2020 12:03PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.