Search code examples
regexpostgresqlsubstring

Extract all substrings bounded by the same characters

Given a name_loc column of text like the following:

{"Charlie – White Plains, NY","Wrigley – Minneapolis, MN","Ana – Decatur, GA"}

I'm trying to extract the names, ideally separated by commas:

Charlie, Wrigley, Ana

I've gotten this far:

SELECT SUBSTRING(CAST(name_loc AS VARCHAR) from '"([^ –]+)')
FROM table;

which returns

Charlie

How can I extend this query to extract all names?

Solution

  • My two cents, though I'm rather new to postgreSQL and I had to copy the 1st piece from @Marth's his answer:

    WITH input(name_loc) AS (
    VALUES ('{"Charlie – White Plains, NY","Wrigley – Minneapolis, MN","Ana – Decatur, GA"}')
         , ('{"Other - somewhere"}') 
)
SELECT REGEXP_REPLACE(name_loc, '{?(,)?"(\w+)[^"]+"}?','\1\2', 'g') FROM input;
    regexp_replace
    Charlie,Wrigley,Ana
    Other