SELECT specific string pattern from mysql table

Question

this is what I know

SELECT domain FROM domain_table GROUP BY domain

Here is a possible result

www.domain.com, domain.com, sub.domain.com, domain.co/a-page

Is it possible to apply regex (or a hack) to the above select statement so that i will get domain.co instead of domain.co/a-page

peterm · Accepted Answer · 2017-05-24 21:17:13Z

1

If I understand your question correctly you can try

SELECT SUBSTRING_INDEX(domain, '/', 1) domain
  FROM domain_table
 GROUP BY SUBSTRING_INDEX(domain, '/', 1)

Here is a dbfiddle demo

Output:

domain
--------------
domain.co
domain.com
sub.domain.com
www.domain.com
--------------

answered May 24, 2017 at 21:09

peterm

93k16 gold badges157 silver badges162 bronze badges

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

1 Comment

i was looking for an php side solution for OP but i'm still faberglasted by how much SQL can do for you.