regexp_replace for oracle to replace https not working

Question

anyone know why does RegExp works on simulators but not really in oracle?

should replace // with / except on https://

SELECT regexp_replace (url_link,'(?<!https:)\/\/','\/'), url_link
FROM URL_TABLE;

changes

https://pet/green//car/plane//garden

to

https://pate/gren/car/plane/gardn

thank you

Answer 1

Use a Non-Colon Character List for the Character Preceding the // in the REGEXPR_REPLACE Pattern String

This is the same as Littlefoot's solution except to make sure we do not replace the first // with the : preceding.

We just indicate that we do not want a match with the non-colon character list, [^:], and then encapsulate this in a character group (place this in a parethesis).

In our replace string, we just reference this character group with \1 which translates as the first character group.

SCOTT@db>SELECT
  2      regexp_replace('https://pet/green//car/plane//garden','([^:])//','\1/')  http_url
  3  FROM
  4      dual;

http_url                                 
------------------------------------
https://pet/green/car/plane/garden

---

Addendum

As a side note to this pattern matching problem, it sure would be nice if Oracle's implementation of regular expression did have (negative) lookahead or (negative) lookbehind.

Here is an example of this problem with Vim's regular expression matching:

\(https:\|http:\)\@<! = negative lookbehind for "https:" or "http:" using alternation operator

\/\/ = double forward slashed pattern

We see matching // highlighted in blue

Answer 2

Not very smart, but works (kind of):

SQL> with test as (select 'https://pet/green//car/plane//garden' url from dual)
  2  --
  3  select
  4    regexp_replace(url, '//', '/', 8) res1,
  5    regexp_replace(url, '//', '/', instr(url, '//') + 1) res2
  6  from test;

RES1                               RES2
---------------------------------- ----------------------------------
https://pet/green/car/plane/garden https://pet/green/car/plane/garden

SQL>

[added Gary_W's suggestion as RES2]