CODEWITHSUNDEEP
mysqlsqlregexselect
user7543621
user7543621

Reputation:

Correct way to use Regexp in select statement in MySQL

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

Upvotes: 0

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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$' ;

Upvotes: 1

Related Questions