Skip to content
Advertisement

Use Regex to Exclude any values that do not have a letter

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

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement