Using regexp with sql to find words that match specific characteristics-CodePudding

I want to write a select statement that gets all the columns where one of them match a certain regular expression.

I basically have this file_name column and I want to select all files that start with a certain word, have a certain word in the middle, and end with 'xlsx'.

I know for checking if if starts with a certain word, I can do

SELECT * FROM files WHERE file_name REGEXP '^company';

to find files that start with the word company, just using that as a place holder.

after this point it can have whatever other characters but at some point in the middle, i am looking for another word, lets say 'dog' and then ends with xlsx.

I know for finding file names that end with xlsx i can do:

SELECT * FROM files WHERE file_name REGEXP 'xlsx[[:>:]]';

My main confusion is how to combine all these 3 cases into one statement. so that it starts with company then at some point has the word dog then after at some point ends with xlsx.

CodePudding user response：

You can combine all conditions into one select using:

SELECT * FROM files WHERE file_name REGEXP '^company.*dog.*xslx$';

CodePudding user response：

If the "words" are not too complex, LIKE will be faster:

SELECT * FROM files WHERE file_name LIKE 'company%dog%xslx';

especially if you have INDEX(file_name).