postgresql: How to concatenate two regexp_matches()

Question

I'm trying to extract both ints and chars from names such as 123A America, 234B Britania. I only want the the number and the attached letter (i.e. 123A) . I'm using regexp_matches(name, '(\d+)(\D)') and it results as:

{123,A}, {456,B}

I thought using concatenation, getting the first element of an array and the second element using two different functions (regexp_matches(name, '(\d+)(\D)' )) [1] || (regexp_matches(name, '(\d+)(\D)' )) [2]

But it generates an error: ERROR: functions and operators can take at most one set argument

How can I get the two element as one string?

What, in general, does the error message refer to? i.e. Why does, what seems to be a simple value concatenation, cause ERROR: functions and operators can take at most one set argument? — pretzels1337
– pretzels1337, Commented Aug 27, 2014 at 17:59

Scott S · Accepted Answer · 2013-10-28 15:38:05Z

1

You don't have to get the two items you're searching for as different sets, just get them as a single set. Remove the )( between \d+ and \D and that will return a set containing the entire string you're looking for.

Results in this - regexp_matches('123A America, 234B Britania', '(\d+\D)' )

This will only find the first match. To get all matching substrings, use the g flag - regexp_matches('123A America, 234B Britania', '(\d+\D)', 'g')

answered Oct 28, 2013 at 15:38

Scott S

2,73618 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pstanton · Accepted Answer · 2015-02-28 01:26:55Z

0

good answer by @Scott S however if you can't achieve what you need within one capture group the solution is to write a function, assign the regexp result to a variable and then use it.

CREATE OR REPLACE FUNCTION do_something(_input character varying)
  RETURNS character varying AS
$BODY$
DECLARE
    matches text[];
BEGIN
    matches := regexp_matches(_input, '^([0-9]{1,}_[^_]{1,})_[a-z]{1,}(.*)$','i');
    return substring(matches[1], 0, 24)||matches[2];
END
$BODY$
  LANGUAGE plpgsql;

answered Feb 28, 2015 at 1:26

pstanton

36.9k28 gold badges132 silver badges171 bronze badges

Collectives™ on Stack Overflow

postgresql: How to concatenate two regexp_matches()

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related