MySQL Regular Expression Behavior

MySQL & MariaDB
1

I am trying to match mysql column output with this regular expression query

select zone from one__zrq_zone where zone regexp ‘^._default.com.test(..*)?$’;

It under matches and shows these results. I am assuming it should match on a ‘.’ following test by instead it matches testsyb[0-2]
±-----------------------+
| zone |
±-----------------------+
| ._default.com.testsyb0 |
| ._default.com.testsyb1 |
| ._default.com.testsyb2 |

Any idea on what may be happening? Thanks

2

Hi Badri, welcome to the community.
Can you provide a sample of your data and what you expect from your regular expression?
I’m asking this because the regular expression you posted is actually valid with the results you show, as the “.” matches any character.

3

Thanks for the quick response

Sorry. somehow the escape () was omitted in reg ex. There is actually a . (escape then period) after ‘(’

mysql> select zone from one__zrq_zone where zone regexp ‘^._default.com.test(..*)?$’;

So this query pulls up these matches
±------------------------------+
| zone |
±------------------------------+
| ._default.com.testsyb0 |
| ._default.com.testsyb1 |
| ._default.com.testsyb2 |
±------------------------------+
I would expect having a . (escaped period) will look for matches with an actual period after test and not testsyb[0-2]. Thank you

4

@Badri_Subramaniam, can you please provide some sample data including values that should match and values that should not match?

5

Here are the column values in the zone table for zone column

Row 1 ._default.com.testsyb1
Row 2 ._default.com.testsyb2
Row 3 ._default.com.testsyb3
Row 4 ._default.com.test
Row 5 ._default.com.test.a
Row 6 abc.com

Here is the regular expression ‘^._default.com.test(..*)?$’
Note: There is an escape after the ‘(’ to escape the first period after ‘(’

The regular expression should only return rows 4 and 5 since it is looking for a period after test. IMHO it should not match any of .default.com.testsyb[0-2]

instead it matches on rows 1-5. Rows 1-3 should not match. Rows 6 should not match and it does not match as seen, Thanks

6

Please use the formatting tools available to show the correct regex, that way there is no guessing. Here is my run. You need to escape the escape:

mysql [localhost:8028] {msandbox} (test) > INSERT INTO regs VALUES 
('._default.com.testsyb1'), 
('._default.com.testsyb2'), 
('._default.com.testsyb3'), 
('._default.com.test'), 
('._default.com.test.a'), 
('abc.com');
Query OK, 0 rows affected (0.11 sec)

mysql [localhost:8028] {msandbox} (test) > SELECT * FROM regs
WHERE zone REGEXP '^._default.com.test(\\..*)?$';
+----------------------+
| zone                 |
+----------------------+
| ._default.com.test   |
| ._default.com.test.a |
+----------------------+
2 rows in set (0.00 sec)
7

Thanks a lot. That fixed it. My C program calls the MySQL API with a ‘\’. Looks like I needed to add another pair. Thanks again very much