Search for substrings / patterns in specific order, regexp? sql mysql

Question

In my MySQL i want to look for characters in a specific ORDER...

the following code doesn't care how 'o' 'l' 'd' appear... so words containing those characters would be matched...

I'm only interested in doing the match if 'o' appears first, 'l' second and 'd' third.

Alternatives to REGEXP are also welcome!

SELECT DISTINCT(col) 
FROM tab 
WHERE col REGEXP 'o'
AND col REGEXP 'l'
AND col REGEXP 'd'
GROUP BY col
ORDER BY col DESC

DISTINCT isn't a function DISTINCT should be used as SELECT DISTINCT col — Raymond Nijland
– Raymond Nijland, Commented Nov 10, 2017 at 18:53

RLessard · Accepted Answer · 2017-11-12 08:10:36Z

1

You should try the following

SELECT col 
FROM tab 
WHERE col like "%o%l%d%"
ORDER BY col DESC

answered Nov 10, 2017 at 18:44

RLessard

1466 bronze badges

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

5 Comments

DISTINCT isn't a function DISTINCT should be used as SELECT DISTINCT col

You are right, good catch Raymond. I updated the query string.

Ricardo, yes the % are wildcards in the like statement.

Don't do both DISTINCT and GROUP BY. And do neither if col is not duplicated.

Good catch Rick James