Thread: Splitting a string containing a numeric value in to three parts
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.
Sanjaya Vithanagama <svithanagama@gmail.com> wrote:
> 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}.
select regexp_matches('hello+123123.453the-123re',
'^(.*?)([\+|-]?(?:\d*(?:(?:\.)?\d+)))(.*)$')
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Thank you Kevin! I missed the start of string and end of string matching.
On Tue, Jul 28, 2015 at 1:06 AM, Kevin Grittner <kgrittn@ymail.com> wrote:
Sanjaya Vithanagama <svithanagama@gmail.com> wrote:
> 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}.
select regexp_matches('hello+123123.453the-123re',
'^(.*?)([\+|-]?(?:\d*(?:(?:\.)?\d+)))(.*)$')
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Sanjaya