Question 191462 in PostgreSQL Question posted in
The official documentation can be found here.

How to extract all words starting with specific character from string in PostgreSQL?

I am trying to extract all words that start from ‘M’ character.

For example I have a string:
"[{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}]"
I want to get: "Mary, Max"

I tried to regex_replace([{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}], ‘W’, ”, ‘g’) and left only words, but don’t know the correct regex to extract all words starting with ‘M’. I also tried this: substring("name Mary type Dog name Max type Dog" from ‘w*Mw*’) but this gives me only one word.

Tags:

2

Answers


  1. 0

    You can use the following pattern.

    :s+"(?=M)(.+?)"

    Output for group 1.

    Mary
Max
    Login or Signup to reply.
  2. 0

    You can use Postgresql’s regexp_matches function. Documentation here.

    This query might help.

    SELECT array_to_string(regexp_matches(
    '[{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}]',
    '"name": "Mw+"',
    'g'
), ', ') AS words;

    It returns a comma separated string. The ‘g’ flag specifies that all results must be returned.

    One other way to get the same result is via LIKE operator.

    SELECT string_agg(value, ', ') AS words
FROM jsonb_array_elements_text(
    '[{"name": "Mary", "type": "Dog"}, {"name": "Max", "type": "Dog"}]'
) AS elem
WHERE value LIKE 'M%'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search