Return everything before 2nd occurrence in a string in PostgreSQL

Question

I want to return all characters before the 2nd occurrence of the character slash '/' (if any) in PostgreSQL.

Input Column:

/apple/orange/banana
/
/mango
/avocado/kiwi

Desired Output Column:

/apple
/
/mango
/avocado

Can anyone help with this please?

Does this answer your question? How to get substring from 4th occurence of a character until the end of given string in PSQL — demo
– demo, Commented Mar 1, 2021 at 15:42

Gordon Linoff · Accepted Answer · 2021-03-01 15:42:29Z

2

One method is regexp_replace():

select t.*,
       regexp_replace(col, '^([^/]*/[^/]*)/.*$', '\1')
from t;

Here is a db<>fiddle.

answered Mar 1, 2021 at 15:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2021-03-01 15:45:13Z

2

You can use substring() with a regex:

select substring(the_column from '(/\w*)')
from the_table

Another alternative would be split_part()

select '/'||split_part(the_column, '/', 2)
from data

answered Mar 1, 2021 at 15:45

user330315