Is there a way to replace the first '/' from a string conditionally?

Question

Using Postgres 9.6, dealing with millions of rows of data. How do I replace forwardslash '/' with a dash '-'? The problem is we have forward slash after a few more words in the string and that we want to keep it, so basically if there are more than 1 forwardslash then replace the first one with a dash and do nothing for the second one and if there is only one forwardslash then do nothing as well.

I have tried split_part() and substring(), but none of them worked. First, I want to know in a select query and then I want a second step to replace it. In the below code, column1 is varchar column and that's where I want to make a change.

Data Example:
Current - someName/abc-def-tn/more-random-names
Expected - someName-abc-def-tn/more-random-names

Note: someName is not a of a fixed length, so cannot use positioning.

select column1, 
reverse(split_part(reverse(column1), '/', 1)) as new_column_part1,
substring(column1, '[^/]*$') as new_url, as new_column_part2
from tableA

with `REGEXP_REPLACE`, you can capture the groups you want and replace the first slash with a dash. — Guillaume F., May 08 '19 at 17:49

Erwin Brandstetter · Answer 1 · 2019-05-08T18:27:33.223

3

regexp_replace() only replaces the first occurrence - unless you add 'g' as optional 4th parameter, then it replaces all occurrences.

You want to replace the 1st occurrence, only if there is a 2nd. Two solutions:

SELECT col
     , CASE WHEN col ~ '/.*/'  -- only where there are two or more
            THEN regexp_replace(col, '/', '-')
            ELSE col  
       END AS new_col
     , regexp_replace(col, '/(?=.*/)', '-') AS new_col2
FROM   tbl;

new_col2 does the same as new_col with a positive lookahead. More elegant, maybe, but I am not sure whether it's faster (or easier to read).

In an actual UPDATE I would do this:

UPDATE tbl
SET    col = regexp_replace(col, '/', '-')
WHERE  col ~ '/.*/';

This has the additional benefit that only rows are touched that actually need it. See:

How do I (or can I) SELECT DISTINCT on multiple columns?

edited May 08 '19 at 18:27

answered May 08 '19 at 17:56

Erwin Brandstetter

605,456
145
1,078
1,228

`and then I want a second step to replace it` .. That suggests you want to `UPDATE` a table to change existing columns? – Erwin Brandstetter May 08 '19 at 18:24
Then consider the addition. – Erwin Brandstetter May 08 '19 at 18:27
Thank you, you are awesome! – knightrider May 08 '19 at 18:33

Murtaza Hussain · Answer 2 · 2019-05-08T18:35:19.823

0

regexp_replace() replaces first occurrence of a string, for example:

regexp_replace('someName/abc-def-tn/more-random-names', '/', '-', '{1}')

let me know if this works

edited May 08 '19 at 18:35

answered May 08 '19 at 17:50

Murtaza Hussain

3,851
24
30

translate('someName/abc-def-tn/more-random-names', '/', ' ') didn't work, because it just translates '/' into no space, I want to replace it with a dash. So, I used translate('someName/abc-def-tn/more-random-names', '/', '-'), but the problem with this is that it translates even the second forwardslash which I don't want. Same goes for REPLACE function. – knightrider May 08 '19 at 18:17
try now and let me know please – Murtaza Hussain May 08 '19 at 18:35

Is there a way to replace the first '/' from a string conditionally?

2 Answers2