Q:

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

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?

A:

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.

