How we can find domain name using MySQL and regular expression

Question

Hey Friends

i am having some list of domains in the DB,like

http://www.masn.com/index.html
http://www.123musiq.com/index.html etc

what i need as out put is

http://www.masn.com
http://www.123musiq.com

how can i do that in regular expression???

Answer 1

In MySQL, regular expressions can match but not return substrings.

You can use SUBSTRING_INDEX:

SELECT  SUBSTRING_INDEX('www.example.com', '/', 1)

, however, it's not protocol prefix safe.

If you are using a mix of prefixed and unprefixed URL's, use this:

SELECT  url RLIKE '^http://',
        CASE
        WHEN url RLIKE '^http://' THEN
                SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 3), '/', -1)
        ELSE
                SUBSTRING_INDEX(url, '/', 1)
        END
FROM    (
        SELECT   'www.example.com/test/test' AS url
        UNION ALL
        SELECT   'http://www.example.com/test'
        ) q

Answer 2

use substring_index

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index

like

SELECT  SUBSTRING_INDEX(urlfield, '/', 1) from mytable