CODEWITHSUNDEEP
sqlregexoracle-database
airboss
airboss

Reputation: 1907

SQL Regex for finding domain from a common name

I have the following sample data set in one of my tables:

sub1.domain.com
domain.com
sub1.sub2.domain.com
*.domain.com

Updated
Also including ccTLD
*.domain.co.uk



is there a way/regex in Oracle that can help me extract just the domain name - "domain.com" from the string.I have been trying a few regexes but it dint work out. Thanks for the help

Upvotes: 0

Views: 3612

Answers (2)

Ludovic Kuty
Ludovic Kuty

Reputation: 4954

You could use the following regex matching something_without_a_dot.something_without_a_dot from the end of the string. You'll get the answer in the first group. If you need the TLD also, you can enclose everything in () except the $.

([^.]+)\.[^.]+$

In SQL, that gives:

SQL> select regexp_replace('sub1.sub2.domain.com', '^.*?([^.]+)\.[^.]+$', '\1') from dual;

REGEXP
------
domain

The non-greedy .*? at the start allows you to ignore the start of the string.

To get the domain name plus the TLD:

SQL> select regexp_replace('sub1.sub2.domain.com', '^.*?([^.]+\.[^.]+)$', '\1') from dual;

REGEXP_REP
----------
domain.com

To take into account co.uk:

SQL> select regexp_replace('sub1.sub2.domain.co.uk', '^.*?([^.]+\.(co\.uk|[^.]+))$', '\1') from dual;

REGEXP_REPLA
------------
domain.co.uk

Upvotes: 1

William Stearns
William Stearns

Reputation: 479

While I was typing my answer the guy above gave a good regex. I'm thinking you could use REGEXP_REPLACE in your select statement.

REGEXP_REPLACE(fieldname, '([^.]+)\.([^.]+)$', '\1\.\2') as fieldname

Upvotes: 0

Related Questions