Extract UoM text between two round brackets only after a specific string

Question

I have a large string in the form of

Anschlüsse/Schnittstellen=(USB 3.0)|Bildschirmgröße=59,9 cm (23,6 Zoll)|Bluetooth=Bluetooth 4.0|

I need to extract 23,6 out of Bildschirmgröße=59,9 cm (23,6 Zoll)|

I tried

/(?<=Bildschirmgröße=)(?:.+?\()(.*?)(?:\))(?=\|)

but that returns 2 matches

59,9 cm (23,6 Zoll)
23,6 Zoll

and in my SQL query I only get the first result instead of the second.

Trying to only get the correct match via

(?<=Bildschirmgröße=)(?:.+?\()(\(.*?\))(?:\))(?=\|)

returns nothing/NULL?

Answer 1

What you get is not actually two matches, you get the whole match value and the Group 1 value.

In order to get just the UoM value, you can use the following PCRE compliant pattern (since Exasol uses PCRE behind its regex functions):

Bildschirmgröße=[^(]*\(\K[^()]+

See the regex demo.

To only get the number, you can use

Bildschirmgröße=[^(]*\(\K\d+(?:,\d+)?

See this regex demo.

Details:

• Bildschirmgröße= - a literal text
• [^(]* - zero or more chars other than (
• \( - a ( char
• \K - a match reset operator that discards the text matched so far from the overall match memory buffer
• [^()]+ - one or more chars other than ( and )
• \d+(?:,\d+)? - one or more digits and then an optional occurrence of , and one or more digits. Note you may use a more lenient and at the same time more universal extraction pattern for UoM like \d[\d,.]*\s+\w+ (see demo).