Mysql LIKE 2nd character and 2nd last character both are 'm'-CodePudding

I try to find query to find a string that 2nd character and 2nd last character both are letter m.

SELECT last_name
FROM employees
WHERE (last_name LIKE '_m%m_' AND LENGTH(last_name) >= '3');

Thanks in advance :)

CodePudding user response：

Why not just OR instead of AND? I don't see the point of AND when your LIKE operator allready rules out names below three characters. You don't need to use regex nor a check for length:

SELECT last_name FROM employees WHERE last_name LIKE '_m_' OR last_name LIKE '_m%m_';

The use of OR and LIKE does catch any string that has at least 3 characters.

If you must use regex, try REGEXP operator:

SELECT last_name FROM employees WHERE last_name REGEXP '^.m(.*m)?.$';

Where the pattern means:

^.m - Start-line anchor with a single character and a literal 'm';
(.*m)? - Optional capture group to match 0 characters upto a literal 'm';
.$ - A single character with end-line anchor.

The benefit of REGEXP is that it's a bit less verbose if you need case-insensitive matching using pattern: '^.[Mm](.*[Mm])?.$'. See an online demo.

CodePudding user response：

If you need all record with second and last character is m you can use the following query:

select * from <table> where <column> like '_m%m'

the _ in the query is a placeholder for one character and % for many characters