Extract hostname from an URL in MySQL with REGEX

user_11930252 0
Viewed 507

I am trying to extract hostnames from URLs by using the MySQL function REGEXP_SUBSTR.

The closest RegEx I got is the following:

(?:\w+\.)?(\w+\.\w+)

Which works relatively speaking, but the issue is that the captured hostname is grouped

Suppose we're trying to match https://www.w3schools.com/home, the above regex would return:

  1. Full match www.w3schools.com
  2. Group 1. w3schools.com

REGEXP_SUBSTR seems to only subtract what is found in the "Full match" thus the above solution results incorrect. How can I modify the above pattern in order to include the hostname in the Full match, rather than a Group?

mysql regex
1 Answers

Assuming the MySQL is version 8, use

REGEXP_SUBSTR(column, '\\w+\\.\\w+(?=/|$)')

See proof

Explanation

--------------------------------------------------------------------------------
  \w+                      word characters (a-z, A-Z, 0-9, _) (1 or
                           more times (matching the most amount
                           possible))
--------------------------------------------------------------------------------
  \.                       '.'
--------------------------------------------------------------------------------
  \w+                      word characters (a-z, A-Z, 0-9, _) (1 or
                           more times (matching the most amount
                           possible))
--------------------------------------------------------------------------------
  (?=                      look ahead to see if there is:
--------------------------------------------------------------------------------
    /                        '/'
--------------------------------------------------------------------------------
   |                        OR
--------------------------------------------------------------------------------
    $                        before an optional \n, and the end of
                             the string
--------------------------------------------------------------------------------
  )                        end of look-ahead
user_113298900
Related