I want to split a given string which could possibly contain a numeric value, using regexp_matches.
The numeric value may contain an optional positive or negative sign, an optional decimal place.
The result should *also* report the non-matching parts before and after the identified numeric values as the first and the last positions of the returned array.
It should identify the first occurrence of a numeric value containing an optional sign and optional decimal places. The non matching parts should be returned as well.
Some example input and expected output values:
'hello+111123.454545world' -> {hello,+111123.454545,world}
'he-lo+111123.454545world' -> {he-lo,+111123.454545,world}
'hel123.5lo+111123.454545world' -> {hel,123.5,lo+111123.454545world}
'hello+111123.454545world' -> {hello,+111123.454545,world}
'hello+111123.454545world' -> {hello,+111123.454545,world}
'1111.15' -> {"",1111.15,""}
'-.234' -> {"",-.234,""}
'hello-.234' -> {hello,-.234,""}
I can match the numeric value and the rest of the string after the numeric value using the following:
select regexp_matches('hello+123123.453the-123re', '([\+|-]?(?:\d*(?:(?:\.)?\d+)))(.*)') outputs array {+123123.453,the-123re}.
Where I am having trouble is with matching the first part of the string. In other words what needs to be 'RE' in the following _expression_ for it to report the all three elements of the array.
select regexp_matches('hello+123123.453the-123re', '((RE)[\+|-]?(?:\d*(?:(?:\.)?\d+)))(.*)') should return array {hello,+123123.453,the-123re}.
Any ideas/pointers of achieving the above?
Thank you,
Sanjaya.
