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?

Please clarify either you want to have both in one row or just if one is enough to select the row? — bonCodigo
– bonCodigo, Commented Jan 12, 2013 at 8:29

bonCodigo · Accepted Answer · 2013-01-12 10:28:13Z

3

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~ |

edited Jan 12, 2013 at 10:28

answered Jan 12, 2013 at 8:31

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bonCodigo Over a year ago

@PaulMG is this what you wanted to get? Please comment. You only need to specify the square bracket around [|] as it was working as an OR in the regex before. Now you treat is as a character to be found.

PaulMrG Over a year ago

Hi @bonCodigo that works in my code thanks. It seems odd to use the backslash before the square bracket so I tried without and that works too. If anything I thought the backslash should be before the pipe like so [\|]. I tried that and it also works... weird

bonCodigo Over a year ago

@PaulMrG the most important thing here is that you need to wrap the | for regex to not mistake is as the operator.. So yours would have worked wiht your current answe as long as you had doubled the `escape syntax the backslash `. Check here [sqlfiddle]( sqlfiddle.com/#!2/47e3c/11) This is more accurate. So I am glad you expriemented and got it worked. Please mark as answer then :) so the community knows that you problem is solved.

Srinivas · Accepted Answer · 2013-01-12 07:28:01Z

0

Did you try this?

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

SQL Fiddle

answered Jan 12, 2013 at 7:28

Srinivas

1,7861 gold badge14 silver badges27 bronze badges

1 Comment

PaulMrG Over a year ago

Yes, but it selects other rows that don't have both substrings, see edit of your fiddle sqlfiddle.com/#!2/4bb8d/1

Collectives™ on Stack Overflow

MySQL REGEXP not matching strings

2 Answers 2

Edit:

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Edit:

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related