CODEWITHSUNDEEP
regexpostgresql
fansz
fansz

Reputation: 225

Extract parts of a string using a Regular Expression in PostgreSQL

I want to extract the first_name and second_name strings but with this regex only get the first_name string.

SELECT
   SUBSTRING (
      'a:2:{i:0;s:14:"first_name";i:1;s:15:"second_name";}',
      '\"[^\"]*\" '
   ) as obs_regex

How do I modify my regex to get first_name second_name as the result?

thanks you

Upvotes: 3

Views: 598

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627537

You may match multiple occurrences using g flag and you need to use a capturing group to get the values without quoation marks:

SELECT
   REGEXP_MATCHES (
      'a:2:{i:0;s:14:"first_name";i:1;s:15:"second_name";}',
      '"([^"]*)"', 'g'
   ) as obs_regex

Result:

enter image description here

To get a concatenated string of matches you need to "convert" the regexp_matches result to an array and use array_to_string:

SELECT ARRAY_TO_STRING (
 ARRAY (
   SELECT REGEXP_MATCHES (
      'a:2:{i:0;s:14:"first_name";i:1;s:15:"second_name";}',
      '"([^"]*)"', 'g'
   )
 ), ' ') as obs_regex

Result:

enter image description here

Upvotes: 5

Related Questions