How to pull out the word I need in a column in Snowflake?

Question

So I have a column in a table as follows:

TBL

JFK 257: Gremlin vs Ronald 2
JFK 271: Walter vs Hamburglar
JFK 272: Pizza vs Papa 1
JFK 272: Pizza vs Papa 2
JFK 267: 
JFK 275: Texas vs Cheese

And I want to pull out the two names that surround the word 'vs'.

What i wish to do is generate a new table of 2 columns with each person that surrounds the vs. That is, just the singular word before (Person1) and after (Person2).

Final outcome:

Person1  Person2
Gremlin  Ronald
Walter   Hamburglar
Pizza    Papa
Pizza    Papa
NA       NA
Texas    Cheese

Answer 1

Another approach without using regex. Load the data:

CREATE TABLE TEMP2 (CUSTOMER VARCHAR);
INSERT INTO TEMP2 VALUES
('JFK 257: Gremlin vs Ronald 2'),
('JFK 271: Walter vs Hamburglar'),
('JFK 272: Pizza vs Papa 1'),
('JFK 272: Pizza vs Papa 2'),
('JFK 267: '),
('JFK 275: Texas vs Cheese');

Then use split parts to get the data past the colon, then again to get it before and after the vs.

Since person2 has an extra space we need to grab everything before the space. If there is no space we will just take the entire string.

WITH RAW_DATA AS (   
SELECT  TRIM(SPLIT_PART(SPLIT_PART(CUSTOMER, ':', 2), 'vs',1)) as person1_raw,
        TRIM(SPLIT_PART(SPLIT_PART(CUSTOMER, ':', 2), 'vs',2)) as person2_raw
     FROM TEMP2)
 SELECT person1_raw AS person_1,
        LEFT(person2_raw, case when position(' ', person2_raw) = 0 then length(person2_raw) else position(' ', person2_raw)  end ) as  person_2
    FROM RAW_DATA     ;  

results:

PERSON_1	PERSON_2
Gremlin	Ronald
Walter	Hamburglar
Pizza	Papa
Pizza	Papa
Texas	Cheese

Answer 2

There's probably an easier way, but this works:

set term = 'JFK 257: Gremlin vs Ronald 2';

select   regexp_substr(trim(regexp_replace(split($TERM, 'vs')[0]::string, '\\s', ' ')), '\\b(\\w+)$') as LEFT_NAME
        ,regexp_substr(trim(regexp_replace(split($TERM, 'vs')[1]::string, '\\s', ' ')), '^([\\w\\-]+)') as RIGHT_NAME