Search code examples
sqlsybase

SQL "excluding" Alphanumeric condition not working

I know there are many forum discussions, but I think my situation is unique, I am trying to look for all the address1 fields which does not have alpha numeric values, I just queried with the basic sql ( Database is Sybase ) , like below 

select  address1 from address_table where address1 like '%[^a-zA-Z0-9]%'

My resultset is including alpha numeric values, 

~~~~~~~~~~~~
1304 LOVERS LN
1304 LOWE AVE
1304 LYNCHBURG ST #1
1304 LYNCHBURG ST #1
1304 LYNNWOOD DR
~~~~~~~~~~~~

I am not sure what am I missing

Solution

  • If I'm understanding your ask correctly, you'll need more specificity in your pattern to match records whose address1 contain no alphanumeric characters anywhere in the string.

    Leverage the greedy repeating modifier * to check each character in the string against the pattern.

    select address1 from address_table where address1 like '%[^a-zA-Z0-9]*%'