Substring from reverse in PostgreSql

Question

How do we substring from reverse in Postgres? In oracle we can provide the no.of occurrences of the pattern and extract the expected records. In Postgres we do not have such option. I tried using substring(), left() and right() functions, still it is not working. Any suggestion would be helpful.

Value in the column,

col1
100~500~~~~Bangalore~~~~KA~null~Train

Expected result,

Train

Do you want the string after the last ~? Or the string between the (n)th and the (n+1)th occurrence of ~? — Laurenz Albe
– Laurenz Albe, Commented Jul 13, 2021 at 9:24

user330315 · Accepted Answer · 2021-07-13 08:29:23Z

3

To get the characters after the last ~ you can use substring() with a regex:

substring(col1 from '~([^~]+)$')

Or get the position of the last ~ by using the reverse function:

right(col1, strpos(reverse(col1), '~') - 1)

A more general approach is to convert the string to an array, then pick the last array element:

(string_to_array(col1, '~'))[cardinality(string_to_array(col1, '~'))]

The best solution to this sort of problems is to not store multiple values delimited by some character in a single column. If you really need to de-normalize using arrays or JSON would at least be a bit more flexible (and robust)

answered Jul 13, 2021 at 8:29

user330315

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

Collectives™ on Stack Overflow

Substring from reverse in PostgreSql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related