Find all matches (decimal) from string in SAP HANA with Regex

user_3677983 0
Viewed 594

I need to extract all matches decimal numbers from string in SAP HANA

the text: LOREN IPSUM DOLOR SIT AMET 73,89 X 339,85 X 0,08 CBC70° 1000/2,5

with

substr_regexpr('(\d(.*?)([\.\,]\d{1,3}))' in "Field" group 1

I extract only 73,89 from text.

with

\d(.*?)([\.\,]\d{1,3})+\d(.+?)([\,]\d{1,3})+\d(.*?)([\,]\d{1,3})

I extract the 3 values together: 73,89 X 339,85 X 0,08

I need to extract this, but I am unable to do so.

substr_regexpr('(\d(.*?)([\.\,]\d{1,3}))' in "Field" group 1 ==> 73,89
substr_regexpr('(\d(.*?)([\.\,]\d{1,3}))' in "Field" group 2 ==> 339,85
substr_regexpr('(\d(.*?)([\.\,]\d{1,3}))' in "Field" group 3 ==> 0,08

Thanks!

hana regex
1 Answers

To get 3 capturing groups as per shown samples, you may try following.

(?<=\s)(\d+(?:,\d+)?)\s+X\s+(\d+(?:,\d+)?)\s+X\s+(\d+(?:,\d+)?)

Demo for above regex

Explanation: Adding detailed explanation for above.

(?<=\s)          ##Checking look behind if spaces are present before next mentioned match.
(\d+(?:,\d+)?)   ##Creating 1st capturing group to match digits and ,digits as optional.
\s+X\s+          ##Matching spaces(1 or more occurrences) X spaces(1 or more occurrences).
(\d+(?:,\d+)?)   ##Creating 2nd capturing group to match digits and ,digits as optional.
\s+X\s+          ##Matching spaces(1 or more occurrences) X spaces(1 or more occurrences).
(\d+(?:,\d+)?)   ##Creating 3rd capturing group to match digits and ,digits as optional.
user_58665800
Related