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[[:>:]]';

regex101.com/r/o1zflx/1 or this stackoverflow.com/a/49134699/460557 with a slight change to remove the dash — Jorge Campos
– Jorge Campos, Commented Jul 21, 2021 at 6:47
thanks @JorgeCampos i need this simplified to be used in a mysql query, i have updated the question above for details — woninana
– woninana, Commented Jul 21, 2021 at 7:00

Jorge Campos · Accepted Answer · 2021-07-21 07:19:47Z

1

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

answered Jul 21, 2021 at 7:19

Jorge Campos

23.9k7 gold badges65 silver badges95 bronze badges

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

Collectives™ on Stack Overflow

Regexp match domain and subdomain only from given domain in mysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related