Split string by comma in BigQuery

Question

I would like to split a string in the BQ row into other rows by comma. But I need to split only in case if it is just a comma without space. As of now, I use, for example,

SELECT SPLIT('Mon,Tue,Wed', ',')

but If I have 'Mon, 23,Tue,Wed, 25' I would like to see results

Mon, 23
Tue
Wed, 25

How can I do it?

Answer 1

Maybe replace , to something else (like *) before split and then replace it back?

select array_agg(replace(splitted, '*', ', '))
from unnest(split(replace('Mon, 23,Tue,Wed, 25', ', ', '*'))) as splitted