Getting first string from a web address

user_19402396 0
Viewed 25

I am trying to get a name from a list of websites and need only the first string or word to be more precise. I have found a way in Python but I am interested in doing it with PostgreSQL

For example I would need happy from https://www.happy.com.

I used following code but I think I am going in a wrong direction:

WITH test AS (SELECT regexp_replace('https://www.happy.com','(https://www.|https://|http://)',''))
SELECT SPLIT_PART(test,'.',0)
postgresql split regex
1 Answers

You can use the following REGEXP_REPLACE:

SELECT REGEXP_REPLACE('https://www.happy.com',
                      '^(?:(?:ht|f)tps?://(?:www\.)?|www\.)(.*)\.[^.]+$',
                      '\1') AS Result;

See the regex demo.

Details:

  • ^ - start of string
  • (?:(?:ht|f)tps?://(?:www\.)?|www\.) - http://www., https://www., ftp://www., ftps://www., https://, http://, ftps://, ftp:// or just www.
  • (.*) - Group 1 (\1): any zero or more chars as many as possible
  • \. - a . char
  • [^.]+ - one or more chars other than a . char
  • $ - end of string.
user_38329700
Related