MySQL use of REGEXP or LIKE to select table name with database alias

Question

I have a table as follows:

id    |                query                 -
------------------------------------------------
1     | SELECT COUNT(*) FROM `test`.`user`;
2     |  INSERT INTO `test`.`user` (id,name,email)
         VALUES      ('4','Don','[email protected]');
3     | SELECT COUNT(*) FROM `test`.`data`;

I want to get rows with "test.user" patterns in the query.

maraca · Accepted Answer · 2015-11-13 13:13:26Z

1

SELECT * FROM ... WHERE LOWER(query) LIKE '%`test`.`user`%'

This assumes you don't have any spaces around the .

answered Nov 13, 2015 at 13:13

maraca

9,5873 gold badges27 silver badges50 bronze badges

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

Comments

ckimbrell · Accepted Answer · 2015-11-13 13:11:39Z

0

You can use LIKE.

SELECT * FROM `tablename` WHERE `query` LIKE '%test%user%'

The % is the wildcard. You can put it at the front, back, or in the middle, and it can be used multiple times. The query above will find anything that has "test" and "user" in that order, with any characters in between, before or after.

answered Nov 13, 2015 at 13:11

ckimbrell

5273 silver badges9 bronze badges

1 Comment

MoopanMec Over a year ago

Check the use case where it fails: SELECT main.id, usr.name FROM test.main main INNER JOIN db.user usr;

the_velour_fog · Accepted Answer · 2015-11-13 13:34:30Z

0

You can get more control over pattern matches in your queries if you use REGEXP, e.g.

select `query` from test WHERE `query` REGEXP '.*test.*user.*';

Gives a basic structure for a query using REGEXP, this works for the question you have asked, but can be modified according to your needs.

As shown in this demo sqlfiddle

For a list of available meta character that work with REGEXP see - http://www.tech-recipes.com/rx/484/use-regular-expressions-in-mysql-select-statements/

answered Nov 13, 2015 at 13:34

the_velour_fog

2,1944 gold badges20 silver badges32 bronze badges

1 Comment

MoopanMec Over a year ago

The above query fails if we have a table name such as "user_duplicate" or "usersomething".

Collectives™ on Stack Overflow

MySQL use of REGEXP or LIKE to select table name with database alias

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related