Postgresql 9: substring with regex to get string between string and pipe

Question

I need to create a query using the SUBSTRING function in Postgresql 9.x to extract a substring between a string and first pipe occurrence.

My source string is something like this:

THIS IS MY EXAMPLE|OTHER EXAMPLE|HELLO: Kevin|OTHER EXAMPLE|OTHER EXAMPLE

So I created this query:

SELECT SUBSTRING(myField from 'HELLO: (.*)\|') AS test FROM myTable

to get the word Kevin between the string 'HELLO: ' and the first occurrence of character pipe, but it doesn't work as intended because it returns Kevin|OTHER EXAMPLE|OTHER EXAMPLE.

Where am I going wrong?

Try using HELLO: ([^|]+), like SELECT SUBSTRING(myField from 'HELLO: ([^|]+)') AS test FROM myTable — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jan 26, 2016 at 14:21
Your pattern is almost right, you just need to understand, that * is greedy. You can use its non-greedy (lazy) counterpart: *?, like 'HELLO: (.*?)\|' (which works in PostgreSQL too). (And of course the posted answer is right too, you can get around greediness with altering the pattern logic too.) — pozs
– pozs, Commented Jan 26, 2016 at 16:19
Did my suggestion work in the end? Please let know if you need more help with this problem. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jul 24, 2023 at 9:00

Wiktor Stribiżew · Accepted Answer · 2016-01-26 14:23:50Z

5

You need to use a negated character class in order not to "overflow" to the next |-separated parts:

SELECT SUBSTRING(myField from 'HELLO: ([^|]+)') AS test FROM myTable

Here is a demo of how this regex works. The [^|]+ pattern matches one or more characters other than a literal pipe.

answered Jan 26, 2016 at 14:23

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Collectives™ on Stack Overflow

Postgresql 9: substring with regex to get string between string and pipe

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related