Splitting a string containing a numeric value in to three parts

From: Sanjaya Vithanagama <svithanagama(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Splitting a string containing a numeric value in to three parts
Date: 2015-07-27 03:48:20
Message-ID: CAMbKYykGpdwRr7JVkZEGu2+gT-CYQJkvrVTP5xWvCOg=Xau=Ew@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2015-07-27 03:48:56 Re: how to compile postgresql with other version of openssl?
Previous Message papa 2015-07-27 03:15:42 Re: I lost my password