For example, column below:
Col_A 1 1,2 the1 TH,E2 THEN 1_Th 11
My desired output would be:
Col_A the1 TH,E2 THEN 1_Th
I tried this but no luck:
UPDATED EDIT:
with new as ( select distinct COL_A test from TABLE_1) select test from new where test regexp '[a-zA-Z]'
Data Type = VARCHAR(16777216)
Advertisement
Answer
on snowflake this works:
SELECT column1 FROM VALUES ('Col_A'), ('1'), ( '1,2'), ( 'the1'), ( 'TH,E2'), ( 'THEN'), ( '1_Th'), ( '11') WHERE column1 RLIKE '.*[a-zA-Z].*';
gives:
COLUMN1 Col_A the1 TH,E2 THEN 1_Th
And given REGEXP is an alias for RLIKE this also works
SELECT column1 FROM VALUES ('Col_A'), ('1'), ( '1,2'), ( 'the1'), ( 'TH,E2'), ( 'THEN'), ( '1_Th'), ( '11') WHERE column1 REGEXP '.*[a-zA-Z].*';
given these behave like LIKE
which is a non-greedy match, the wildcards are needed to match all the prior and after tokens.
WHERE column1 REGEXP '.*[:alpha:]*';
also works