Extract part of a string in Postgres

Question

I have a question how to extract a substring.

Here an example string:

{{from,005021889988,,Amt},{setup-from,66,,},{alert-to,66,ConTeach Zentrale,ConTeach Zentrale},{conn-to,66,ConTeach Zentrale,ConTeach Zentrale},{transfer-to,10,kb,Kai Büsing},{conn-to,66,ConTeach Zentrale,ConTeach Zentrale {disc-to,,,},{rel-from,66,ConTeach Zentrale,ConTeach Zentrale}}

The goal is to extract '66,ConTeach Zentrale,ConTeach Zentrale' between '{conn-to,' and '},'

Any ideas?

• markp-fuso: what query(s) have you tried so far? I see 2x '{conn-to' snippets in your example string so how do you determine which one to extract?

Answer 1

You can use the function substring() with a regular expression. But you need to define your requirements exactly. Like markp commented, there is ambiguity with multiple matches.

SELECT substring($string FROM '{conn-to,(.*?)},')

The part between parentheses is returned. This returns the first match, and the non-greedy quantifier *? prefers the shortest match.