CODEWITHSUNDEEP
sqlstringpostgresqldelimiter
htols
htols

Reputation: 33

Return everything before 2nd occurrence in a string in PostgreSQL

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?

Upvotes: 3

Views: 4694

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271191

One method is regexp_replace():

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

Here is a db<>fiddle.

Upvotes: 2

Related Questions