MySQL REGEXP not matching strings

Question

I'm doing a MySQL query where I need to match two strings within a long string but can't get it to work. This is what I've tried.

SELECT * FROM mytable WHERE (mycol REGEXP '/~20\|2~/' AND mycol REGEXP '/~14\|1~/')

Here is what the string looks like

~20|2~14|1~15|1~16|1~1|1397|1|0:0:0:0||~17|1~18|1~

I want to select all rows that contain both of these substrings

~20|2~ AND ~14|1~

What am I doing wrong?

Answer 1

Check this out please, thsi is only selecting the rows that has both your strings.

• Edit:

The reason here that your original regex didn't work:

Because MySQL uses the C escape syntax in strings (for example, “\n” to represent the newline character), you must double any “\” that you use in your REGEXP strings.

• SQLFIDDLE DEMO2

Query:

SELECT * FROM vendor 
WHERE (vname REGEXP '~20\\|2~' 
AND vname REGEXP '~14\\|1~')
;

---

• SQLFIDDLE DEMO

Query: Notice that I have used a different table/sample data than yours. But copied your data row and changed a bit to trigger the correct regex.

Sample Data:

| VID |                                              VNAME |
------------------------------------------------------------
|   1 | ~20|2~14|1~15|1~16|1~1|1397|1|0:0:0:0||~17|1~18|1~ |
|   2 |               ~20|2~14|1397|1|0:0:0:0||~17|1~18|1~ |
|   3 | ~20|2~14|1~15|1~16|1~1|1397|1|0:0:0:0||~17|1~18|1~ |
|   4 |       ~20|2~1|1~16|1~1|1397|1|0:0:0:0||~17|1~18|1~ |

Query:

SELECT * FROM vendor 
WHERE (vname REGEXP '~20\[|]2~' 
AND vname REGEXP '~14\[|]1~')
;

Results:

| VID |                                              VNAME |
------------------------------------------------------------
|   1 | ~20|2~14|1~15|1~16|1~1|1397|1|0:0:0:0||~17|1~18|1~ |
|   3 | ~20|2~14|1~15|1~16|1~1|1397|1|0:0:0:0||~17|1~18|1~ |

Answer 2

Did you try this?

SELECT * FROM mytable WHERE (mycol REGEXP '~20\|2~' AND mycol REGEXP '~14\|1~')

SQL Fiddle