Get substring from a column with delimiter and varying number of elements

Question

A column in my table in Postgres has varchar values in the format: 'str1/str2' or 'str1/str2/str3' where str represents any string.

I want to write a select query which will return me str2. I surfed but couldn't find any proper function.

Answer 1

Use split_part():

SELECT split_part(col, '/', 2) AS result
FROM tbl;

As Victoria pointed out, the index is 1-based,

Obviously, the delimiter needs to be unambiguous. It (/ in your example) cannot cannot be part of a substring. (Unless that's to the right of what you extract, which is ignored anyway.)

Related:

• Split comma separated column data into additional columns