Split String having commas or 'and ' in one string

select split(‘Ben Hobson, Jane McGuire and Kerry McCarthy’,’,’)
returns

Ben Hobson

Jane McGuire and Kerry McCarthy

expected output :

Ben Hobson

Jane McGuire

Kerry McCarthy

How do I achieve this in BigQuery

>Solution :

Consider below

SELECT SPLIT(REPLACE('Ben Hobson, Jane McGuire and Kerry McCarthy','and', ','))

Note that you need to TRIM each name to remove spaces around it with above query.

or, if a comma followed by a space is guaranteed, you can try this instead.

SELECT SPLIT(REPLACE('Ben Hobson, Jane McGuire and Kerry McCarthy',' and', ','), ', ')