How we can find domain name using MySQL and regular expression

Question

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

Answer 3

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('http://www.domain.com/', '://', -1),'/', 1);

Result: www.domain.com

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX('http://www.domain.com/', '://', -1),'/',1),'www.', -1);

Result: domain.com