Snowflake Regular Expression

You can use

trim(regexp_replace(regexp_replace(col, '"name":\\s*"([^"]+)"|.', '\\1,'), ',+', ','), ',')

Details:

• "name":\s*"([^"]+)"|. regex matches "name":, then zero or more whitespaces, and a ", and then captures into Group 1 any one or more chars other than " and then matches a " char, and replaces with Group 1 and a comma
• The second regexp_replace shrinks all commas into a single occurrence of a comma, ,+ matches one or more commas (you may also use a more specific ,{2,} pattern here instead)
• trim removes commas from start and end.

So pushing you text blob into a CTE.

with data as (
    SELECT * FROM VALUES
    ('[{"entryListId": 3279,"id": 4617,"name": "SpecTra","type": 0},{"entryListId": 3279,"id": 7455,"name": "Signal Capital Partners","type": 0}]')
    t(str)
)

I cannot help but note it's JSON, so lets PARSE_JSON that and then FLATTEN it, and here are you "names"

select 
    d.*
    ,f.value:name::text as name
from data d
    ,table(flatten(input=>parse_json(d.str))) f

giving:

And thus to aggregate, using LISTAGG

select 
    listagg(f.value:name::text, ',') as names
from data d
    ,table(flatten(input=>parse_json(d.str))) f

gives:

Duplicate data:

you can add DISTINCT to the LISTAGG and have only the distinct values kept, but given it is a cost, I did point it out, and you didn't mention duplicate data.

with data as (
    SELECT * FROM VALUES
    ('[
    {
"entryListId": 3279,
"id": 4617,
"name": "SpecTra",
"type": 0
},   
{
"entryListId": 3279,
"id": 4617,
"name": "SpecTra",
"type": 0
},
{
"entryListId": 3279,
"id": 7455,
"name": "Signal Capital Partners",
"type": 0
}]')
    t(str)
)
select 
    listagg(distinct f.value:name::text, ',') as names
from data d
    ,table(flatten(input=>parse_json(d.str))) f;

gives:

Where-as that regex solution does not handle this case:

with data as (
    SELECT * FROM VALUES
    ('[
    {
"entryListId": 3279,
"id": 4617,
"name": "SpecTra",
"type": 0
},   
{
"entryListId": 3279,
"id": 4617,
"name": "SpecTra",
"type": 0
},
{
"entryListId": 3279,
"id": 7455,
"name": "Signal Capital Partners",
"type": 0
}]')
    t(str)
)
select 
    trim(regexp_replace(regexp_replace(d.str, '"name":\\s*"([^"]+)"|.', '\\1,'), ',+', ','), ',') as regexp_replace
from data d

gives: