Extract parts of a string using a Regular Expression in PostgreSQL

Question

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

I would use split_part() for that

user330315
– user330315

2019-08-27 07:19:25 +00:00
Commented Aug 27, 2019 at 7:19 — user330315
– user330315, Commented Aug 27, 2019 at 7:19
Did my solution work for you?

Wiktor Stribiżew
– Wiktor Stribiżew

2019-08-28 06:49:26 +00:00
Commented Aug 28, 2019 at 6:49 — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Aug 28, 2019 at 6:49

Wiktor Stribiżew · Accepted Answer · 2019-08-27 07:19:20Z

5

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:

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:

answered Aug 27, 2019 at 7:19

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Collectives™ on Stack Overflow

Extract parts of a string using a Regular Expression in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related