Correct way to use Regexp in select statement in MySQL

Question

In MySQL I want querying the information schema to find tables in a particular database.

I am using Regexp in the select statement like below.

select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='testing' and TABLE_NAME REGEXP 'test|test_10'

Output:

123_test
123_test_10
temp_test
temp_test_10

Here I am getting list of tables which end with test and test_10 as indicated in the select statement.

I am getting the same result while using below.

select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='testing' and TABLE_NAME REGEXP 'test'

Output:

123_test
123_test_10
temp_test
temp_test_10

Using the second statement how can I get only list of tables that end with 'test`.

The expected output I want is

Expected Output:

123_test

Also I would like to exclude some tables in the list. Like exclude the tables that start with temp and end with test

show some sample input and the expected output (all exceptions included) — Vamsi Prabhala
– Vamsi Prabhala, Commented May 23, 2017 at 20:59
You are only looking for test not specific to the end of the string. The test_10 is redundant (per provided regex). TABLE_NAME REGEXP '(test|test_10)$' would be ending with test or test_10. — chris85
– chris85, Commented May 23, 2017 at 21:00
Are these test and temp placeholders or just literal values? I mean, LIKE is the best option if these are literal substrings. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 23, 2017 at 21:05
@Active_user . . . I think temp_test should also be returned. — Gordon Linoff
– Gordon Linoff, Commented May 23, 2017 at 21:09
Are you ever actually looking for test_10? it doesn't seem like you ever actually want that. — chris85
– chris85, Commented May 23, 2017 at 21:09

Gordon Linoff · Accepted Answer · 2017-05-23 21:08:07Z

1

If you only want table names then end in test, don't bother with regexp. Use like:

select TABLE_NAME
from information_schema.tables
where TABLE_SCHEMA = 'testing' and TABLE_NAME LIKE '%test' ;

Why is like preferable? First, it is standard SQL. Second, under some circumstances (this isn't one of them) it can be optimized using indexes.

As for the regular express, just specify that the expression needs to be at the end of the name:

where TABLE_SCHEMA = 'testing' and TABLE_NAME REGEXP 'test$' ;

answered May 23, 2017 at 21:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

I am getting the partial result of my problem. How do I exclude the temp_test in the statement itself

@Active_user maybe add and TABLE_NAME NOT LIKE 'temp%'