3

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?

htols
  • 33
  • 1
  • 4
  • 1
    Does this answer your question? [How to get substring from 4th occurence of a character until the end of given string in PSQL](https://stackoverflow.com/questions/45632213/how-to-get-substring-from-4th-occurence-of-a-character-until-the-end-of-given-st) – demo Mar 01 '21 at 15:42

2 Answers2

2

One method is regexp_replace():

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

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

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