CODEWITHSUNDEEP
phpmysqlregex
MoopanMec
MoopanMec

Reputation: 47

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

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.

Upvotes: 1

Views: 476

Answers (3)

the_velour_fog
the_velour_fog

Reputation: 2184

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/

Upvotes: 0

maraca
maraca

Reputation: 8743

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

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

Upvotes: 1

ckimbrell
ckimbrell

Reputation: 527

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.

Upvotes: 0

Related Questions