How can i find out records from a column in a table in Snowflake which does not have any alphabets as well any number?

Question

I have a table and within that in a specific column, i need to find out the records which are having characters anything except an alphabets or numbers. I tried using regex as '[^A-Za-z0-9 ]'

Am i doing it correctly?

I have a table and within that in a specific column, i need to find out the records which are having characters anything except an alphabets or numbers.

Answer 1

So your REGEXP is correct, accept Snowflake does an odd thing with REGEXP command and implicitly put start/end anchors, as noted in the doc's

The function implicitly anchors a pattern at both ends (i.e. '' automatically becomes '^$', and 'ABC' automatically becomes '^ABC$'). To match any string starting with ABC, the pattern would be 'ABC.*'.

So you need to put wild match tokens in:

select 
    $1 as str
    ,str regexp '[^A-Za-z0-9 ]' as r1
    ,str regexp '.*[^A-Za-z0-9 ].*' as r2
from values
    ('abcd'), -- good
    ('123'), -- good
    ('abc123'), -- good
    ('!@#$'), -- bad
    ('a!b@c#1$3') -- bad

STR	R1	R1
abcd	FALSE	FALSE
123	FALSE	FALSE
abc123	FALSE	FALSE
!@#$	FALSE	TRUE
a!b@c#1$3	FALSE	TRUE

so then you can use that in a WHERE clause