Snowflake - Check string is alpha numeric or not?

Question

I have a column that should not contain any alphanumeric value. I have tried

case when col1 like '%[0-9]%' 
    then 1 
    else 0 
end

but this is not working.

user_10676716 · Answer

like does not understand the pattern that you are giving it. You want a regex match, so use a regex function:

select col1, regexp_like(col1, '.*[0-9].*') has_number from mytable

This gives you a boolean value (true / false). If you want a number, you can cast or use a case expression instead.

This checks if the string contains any number. If you want to search for any alphanumeric character, then \w comes handy:

regexp_like(col1, '.*\w.*')

And finally if you want to ensure that the string contains only alphanumeric characters:

regexp_like(col1, '^\w*$')

user_1142368 · Answer

Your statement doesn't work because you need to use a regexp keyword to use regexps, and your regexp is wrong.

select ...
from   ...
where
  lower(field) regexp '[a-z0-9]+'