Question 354483 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – After 5 occurrences, replace all forward slashes with a hyphen

Using PostgreSQL, how can I replace all forward slashes by a hyphen after 5 occurrences of a forward slash like so:

https://this/is/a/very/long/url

to this :

https://this/is/a/very-long-url
Tags:

2

Answers


  1. 0

    You can combine regexp_replace() with regexp_instr(). The latter tells you where’s the 6th forward slash /, the former starts at that position and continues replacements until the end of the string: demo

    select regexp_replace('https://this/is/a/very/long/url',
                      '/',
                      '-',
                      regexp_instr('https://this/is/a/very/long/url',
                                   '/',1,6),
                      0);
    regexp_replace
    https://this/is/a/very-long-url

    You could probably reduce this to just the regexp_replace() if you change the pattern to include groups of anything followed by a /, and tell it to replace just the slash, keeping the rest, from the 6th group onward.

    Login or Signup to reply.
  2. 0

    A single regexp_replace() with a smart pattern does it:

    SELECT regexp_replace(url, '(?<=(.*?/){5})(.*?)/', '1-', 'g');

    fiddle

    url can be any string, not necessarily an URL. Every slash (/) after the first 5 is replaced with a hyphen (-).

    The regular expression explained:

    (?<=re) .. positive lookbehind (match only after this expression)
    .*? .. ‘.’ matches any character, *? is the non-greedy quantifier "0-n times, as few as possible"
    {5} .. quantifier for the preceding atom

    1 .. in the replacement is a back reference to the captured match.

    'g' .. The final parameter ‘g’ stands for "globally", i.e. replace all matches, not just the first one.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search