1

How to get the more than one matched keywords in a given string. Please find the below query. 

 SELECT regexp_matches(UPPER('bakerybaking'),'BAKERY|BAKING');

output: "{BAKERY}"

the above scenario given string is matched with two keywords. when i execute the above query getting only one keyword only. How to get other matched keywords.

Vivek S.
  • 19,945
  • 7
  • 68
  • 85
user2803461
  • 21
  • 2

3 Answers3

3

g is a global search flag using in regex.Is used to get all the matching strings 

select regexp_matches(UPPER('bakerybaking'),'BAKERY|BAKING','g')



regexp_matches 
text[]
-------------- 
{BAKERY}       
{BAKING}

to get the result as a single row :

SELECT ARRAY(select array_to_string(regexp_matches(UPPER('bakerybaking'),'BAKERY|BAKING','g'),''));

array 
text[]          
--------------- 
{BAKERY,BAKING}

by using unnest - to convert the array returned to a table

select unnest(regexp_matches(UPPER('bakerybaking'),'BAKERY|BAKING','g'))

unnest 
text
------ 
BAKERY 
BAKING
Vivek S.
  • 19,945
  • 7
  • 68
  • 85
0

accoring to: http://www.postgresql.org/docs/9.5/static/functions-string.html

SELECT regexp_matches(UPPER('bakerybaking'),'(BAKERY)(BAKING)');

Otput:)

regexp_matches ----------------- {BAKERY,BAKING} (1 row)

Vivek S.
  • 19,945
  • 7
  • 68
  • 85
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • side note: since OP has not specified PostgreSQL version.Try to post latest version of PostgreSQL's Documentation (should be http://www.postgresql.org/docs/9.4/static/functions-string.html) – Vivek S. Mar 18 '15 at 06:39
0

Oh the humanity. Please thank me.

--https://stackoverflow.com/questions/52178844/get-second-match-from-regexp-matches-results
--https://stackoverflow.com/questions/24274394/postgresql-8-2-how-to-get-a-string-representation-of-any-array
CREATE OR REPLACE FUNCTION aaa(anyarray,Integer, text)
RETURNS SETOF text
LANGUAGE plpgsql
AS $function$
DECLARE s $1%type;
BEGIN
FOREACH s SLICE 1 IN ARRAY $1[$2:$2] LOOP
RETURN NEXT array_to_string(s,$3);
END LOOP;
RETURN;
END;
$function$;

--SELECT aaa((ARRAY(SELECT unnest(regexp_matches('=If(If(E_Reports_@ >=1, DMT(E_Date_R1_@, DateShift),0)', '(\w+_@)|([0-9]+)','g'))::TEXT)),1,',')
--select (array[1,2,3,4,5,6])[2:5];
SELECT aaa(array_remove(Array(SELECT unnest(regexp_matches('=If(If(E_Reports_@ >=1, DMT(E_Date_R1_@, DateShift),0)', '(\w+_@)|([0-9]+)','g'))::TEXT), Null),3,',')
Adz
  • 49
  • 1