Regexp match domain and subdomain only from given domain in mysql

Question

This is what I got so far I'm getting some status to fail;

[[:<:]]lms.com[[:>:]]

These are my expected status:

Keyword	status
lms.com	1
live.lms.com	1
live-lms.com	0
cssslms.com	0
lmsccc.com	0
lms.com.ph	0
lms.com.edu	0

SELECT 
*
 FROM
(SELECT 
    SUBSTRING_INDEX(email, '@', - 1) AS substrEmail1,
    email
FROM
    members m1) AS d
 WHERE d.substrEmail1 regexp '[[:<:]]gs.com[[:>:]]';

https://regex101.com/r/o1zflx/1 or this https://stackoverflow.com/a/49134699/460557 with a slight change to remove the dash — Jorge Campos, Jul 21 '21 at 06:47
thanks @JorgeCampos i need this simplified to be used in a mysql query, i have updated the question above for details — woninana, Jul 21 '21 at 07:00
Or this variant which also works: http://sqlfiddle.com/#!9/a51de/2 — Jorge Campos, Jul 21 '21 at 07:15

score 1 · Accepted Answer · answered Jul 21 '21 at 07:19

Here is my answer, provided also in the comments.

Either:

select * from test
where url REGEXP '^([[:alpha:]]+\\.lms|lms)\\.com$' = 1;

Or

select * from test
where url REGEXP '^([[:alpha:]]+\\.)?lms\\.com$' = 1

See it working here: http://sqlfiddle.com/#!9/a51de/2

Regexp match domain and subdomain only from given domain in mysql

1 Answers1