Oracle SQL regexp_replace stops at OR group

Question

I try to filter with oracle SQLs regexp_replace a domain name from a list of URLs. The Problem seems to be that some of them do have Ports ans some do not have them.

From the following example the the-super.hosting.com should be replaced with HOSTNAME (but not hard coded in the regexpr as there could be anything)

WITH strings AS (   
  SELECT 'http://wwww11.the-super.hosting.com:9999/aPath/servlet?config=abcLoginNr=%1' s FROM dual union all   
  SELECT 'http://wwww22.the-super.hosting.com:6666/aPath/servlet?config=abcLoginNr=%2' s FROM dual union all   
  SELECT 'http://wwww22.the-super.hosting.com:6666/aPath/servlet?config=abcLoginNr=%2' s FROM dual union all   
  SELECT 'http://wwww04.the-super.hosting.com/aPath/servlet?config#here' s FROM dual   
)  
  SELECT regexp_replace(s,'([[:alpha:]]+://[[:alpha:]]{4}[[:digit:]]{2}\.)(.+)(:9999/|:6666/|/?)(.+)', '\1HOSTNAME\3\4') "MODIFIED_STRING", s "STRING"
  FROM strings;

It seems it cant handle the ports as optional with the normal path (as there the path starts directly).
Is it possible to match the domain part differently so that always the left over will be the path with the optional port?
is there a way to get it replaced with one statement?

I think first (.+) should be ([^/]+?).

revo
– revo

2019-04-11 17:06:27 +00:00
Commented Apr 11, 2019 at 17:06 — revo
– revo, Commented Apr 11, 2019 at 17:06

Alex Poole · Accepted Answer · 2019-04-12 07:51:55Z

I think you're making it more complicated that it needs to be. You only really need three parts; the initial protocol (anything followed by ://) and the www??. prefix (assuming that is actually always present); the rest of the domain name to remove; and everything that's left, which may or may not include a port - but you don't really care; so:

([^.]+\.)([^/:]+)(.*)

where

([^.]+\.) is the protocol and everything up to and including the first dot in the domain name;
([^/:]+) is anything up to either a slash or colon
(.*) is the rest

And for the replacement you want to keep the first and third parts as they are, and replace the second part with your fixed HOSTNAME.

So you get:

WITH strings AS (
  SELECT 'http://wwww11.the-super.hosting.com:9999/aPath/servlet?config=abcLoginNr=%1' s FROM dual union all
  SELECT 'http://wwww22.the-super.hosting.com:6666/aPath/servlet?config=abcLoginNr=%2' s FROM dual union all
  SELECT 'http://wwww22.the-super.hosting.com:6666/aPath/servlet?config=abcLoginNr=%2' s FROM dual union all
  SELECT 'http://wwww04.the-super.hosting.com/aPath/servlet?config#here' s FROM dual union all
  SELECT 'http://wwww04.the-super.hosting.com' s FROM dual union all
  SELECT 'http://wwww04.the-super.hosting.com/' s FROM dual union all
  SELECT 'http://wwww04.the-super.hosting.com/aPath' s FROM dual union all
  SELECT 'http://wwww04.the-super.hosting.com:1234' s FROM dual union all
  SELECT 'http://wwww04.the-super.hosting.com:1234/' s FROM dual union all
  SELECT 'http://wwww04.the-super.hosting.com:1234/aPath' s FROM dual
)  
SELECT regexp_replace(s, '([^.]+\.)([^/:]+)(.*)', '\1HOSTNAME\3') "MODIFIED_STRING", s "STRING"
FROM strings;

MODIFIED_STRING                                                STRING                                                                     
-------------------------------------------------------------- ---------------------------------------------------------------------------
http://wwww11.HOSTNAME:9999/aPath/servlet?config=abcLoginNr=%1 http://wwww11.the-super.hosting.com:9999/aPath/servlet?config=abcLoginNr=%1
http://wwww22.HOSTNAME:6666/aPath/servlet?config=abcLoginNr=%2 http://wwww22.the-super.hosting.com:6666/aPath/servlet?config=abcLoginNr=%2
http://wwww22.HOSTNAME:6666/aPath/servlet?config=abcLoginNr=%2 http://wwww22.the-super.hosting.com:6666/aPath/servlet?config=abcLoginNr=%2
http://wwww04.HOSTNAME/aPath/servlet?config#here               http://wwww04.the-super.hosting.com/aPath/servlet?config#here              
http://wwww04.HOSTNAME                                         http://wwww04.the-super.hosting.com                                        
http://wwww04.HOSTNAME/                                        http://wwww04.the-super.hosting.com/                                       
http://wwww04.HOSTNAME/aPath                                   http://wwww04.the-super.hosting.com/aPath                                  
http://wwww04.HOSTNAME:1234                                    http://wwww04.the-super.hosting.com:1234                                   
http://wwww04.HOSTNAME:1234/                                   http://wwww04.the-super.hosting.com:1234/                                  
http://wwww04.HOSTNAME:1234/aPath                              http://wwww04.the-super.hosting.com:1234/aPath

You can be more explicit about the protocol format etc. but I'm not sure there's much point.

The problem with your original pattern is a mix of greediness and the optional slash as the final 'or' component with the port numbers. You can tweak it to make it work, at least for your sample data, e.g.:

WITH strings AS (   
  SELECT 'http://wwww11.the-super.hosting.com:9999/aPath/servlet?config=abcLoginNr=%1' s FROM dual union all   
  SELECT 'http://wwww22.the-super.hosting.com:6666/aPath/servlet?config=abcLoginNr=%2' s FROM dual union all   
  SELECT 'http://wwww22.the-super.hosting.com:6666/aPath/servlet?config=abcLoginNr=%2' s FROM dual union all   
  SELECT 'http://wwww04.the-super.hosting.com/aPath/servlet?config#here' s FROM dual   
)  
SELECT regexp_replace(s,'([[:alpha:]]+://[[:alpha:]]{4}[[:digit:]]{2}\.)(.+?)(:9999/|:6666/|/)(.+)$', '\1HOSTNAME\3\4') "MODIFIED_STRING", s "STRING"
--                                                                         ^               ^^^    ^
FROM strings;

MODIFIED_STRING                                                STRING                                                                     
-------------------------------------------------------------- ---------------------------------------------------------------------------
http://wwww11.HOSTNAME:9999/aPath/servlet?config=abcLoginNr=%1 http://wwww11.the-super.hosting.com:9999/aPath/servlet?config=abcLoginNr=%1
http://wwww22.HOSTNAME:6666/aPath/servlet?config=abcLoginNr=%2 http://wwww22.the-super.hosting.com:6666/aPath/servlet?config=abcLoginNr=%2
http://wwww22.HOSTNAME:6666/aPath/servlet?config=abcLoginNr=%2 http://wwww22.the-super.hosting.com:6666/aPath/servlet?config=abcLoginNr=%2
http://wwww04.HOSTNAME/aPath/servlet?config#here               http://wwww04.the-super.hosting.com/aPath/servlet?config#here

but it seems like overkill.

really nice - I pick your first solution not my improved one - looks more simple. There is just one question left. why is there the dot needed in the output string? I thought it would be used form the first pattern match part.
@PaulEdison - the pattern I used for the first group was for anything except a dot, so the first dot went into the second group - and thus had to be part of the replacement. You can do it either way though; I've updated to include it in the first group instead, so it isn't part of the second group and doesn't need to be part of the fixed replacement string.

Collectives™ on Stack Overflow

Oracle SQL regexp_replace stops at OR group

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related