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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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