Regex for PostgreSQL for getting domain with sub-domain from URL/Website

Question

Basically, I need to get those rows which contain domain and subdomain name from a URL or the whole website name excluding www.

My DB table looks like this:

+----------+------------------------+
|    id    |    website             |
+----------+------------------------+
| 1        | https://www.google.com |
+----------+------------------------+
| 2        | http://www.google.co.in|
+----------+------------------------+
| 3        | www.google.com         |
+----------+------------------------+
| 4        | www.google.co.in       |
+----------+------------------------+
| 5        | google.com             |
+----------+------------------------+
| 6        | google.co.in           |
+----------+------------------------+
| 7        | http://google.co.in    |
+----------+------------------------+

Expected output:

google.com
google.co.in
google.com
google.co.in
google.com
google.co.in
google.co.in

My Postgres Query looks like this:

select id, substring(website from '.*://([^/]*)') as website_domain from contacts

But above query give blank websites. So, how I can get the desired output?

google.com has no subdomain AFAIK...so why does it appear in your expected output? — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 28, 2017 at 10:21
but in some cases website doesn't contain www. then I need the whole thing like google.com or google.co.in — Shubham Srivastava
– Shubham Srivastava, Commented Nov 28, 2017 at 10:28

naXa stands with Ukraine · Accepted Answer · 2022-02-04 14:48:37Z

23

You must use the "non capturing" match ?: to cope with the non "http://" websites.

like

select 
  id, 
  substring(website from '(?:.*://)?(?:www\.)?([^/?]*)') as website_domain     
from contacts;

SQL Fiddle: http://sqlfiddle.com/#!17/f890c/2/0

PostgreSQL's regular expressions: https://www.postgresql.org/docs/9.3/functions-matching.html#POSIX-ATOMS-TABLE

edited Feb 4, 2022 at 14:48

naXa stands with Ukraine

38.7k25 gold badges208 silver badges279 bronze badges

answered Nov 28, 2017 at 10:29

eMerzh

6251 gold badge5 silver badges12 bronze badges

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

3 Comments

Shubham Srivastava Over a year ago

In this case, the website contains www. need to remove this also

eMerzh Over a year ago

The www is an extand of this technique, i've update the answer

Shubham Srivastava Over a year ago

which is good technique substring or REGEX_REPLACE(as in above ans) because the query need to be optimize because this query works on large amount of data

Wiktor Stribiżew · Accepted Answer · 2017-11-28 10:47:33Z

13

You may use

SELECT REGEXP_REPLACE(website, '^(https?://)?(www\.)?', '') from tbl;

See the regex demo.

Details

^ - start of string
(https?://)? - 1 or 0 occurrences of http:// or https://
(www\.)? - 1 or 0 occurrences of www.

See the PostgreSQL demo:

CREATE TABLE tb1
    (website character varying)
;

INSERT INTO tb1
    (website)
VALUES
    ('https://www.google.com'),
    ('http://www.google.co.in'),
    ('www.google.com'),
    ('www.google.co.in'),
    ('google.com'),
    ('google.co.in'),
    ('http://google.co.in')
;

SELECT REGEXP_REPLACE(website, '^(https?://)?(www\.)?', '') from tb1;

Result:

answered Nov 28, 2017 at 10:47

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

2 Comments

Shubham Srivastava Over a year ago

Thanks. This also works for me. One more thing my friend and me working on the same project but due to C# he is using SQLite(MySQL). So Can you also help me because REGEXP_REPLACE is not supporting MySQL

Wiktor Stribiżew Over a year ago

@ShubhamSrivastava In C#, with MS SQL, you need to use an UDF to be able to use regex. See this answer that should help to get started. Also, you might want to use this script.

Collectives™ on Stack Overflow

Regex for PostgreSQL for getting domain with sub-domain from URL/Website

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related