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.

Answer 1

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*$')