locked
expect to have a given function RRS feed

  • Question

  • Hi,

    Within Sql server, is there any given functions by that we can directly check that one string is full of only alphabets or alphanumerics?


    Many Thanks & Best Regards, HuaMin Chen
    Monday, April 11, 2011 3:07 AM

Answers

  • See this example using LIKE with a pattern:

    declare @t table (String varchar(100))
    insert into @t values ('12345678989013293'),('asdvcmw1221323234'), ('!@#$&*92190247384dskdjdkfhdfh')
    
    select String, Case when String NOT LIKE '%[^0-9]%' then 1 else 0 end as NumbersOnly,
    Case when String NOT LIKE '%[^0-9a-z]%' then 1 else 0 end as AlphaNumbersOnly
    from @t 
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Jackson_1990 Monday, April 11, 2011 4:08 AM
    Monday, April 11, 2011 3:17 AM

All replies

  • See this example using LIKE with a pattern:

    declare @t table (String varchar(100))
    insert into @t values ('12345678989013293'),('asdvcmw1221323234'), ('!@#$&*92190247384dskdjdkfhdfh')
    
    select String, Case when String NOT LIKE '%[^0-9]%' then 1 else 0 end as NumbersOnly,
    Case when String NOT LIKE '%[^0-9a-z]%' then 1 else 0 end as AlphaNumbersOnly
    from @t 
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Jackson_1990 Monday, April 11, 2011 4:08 AM
    Monday, April 11, 2011 3:17 AM
  • declare @t table (String varchar(100))
    insert into @t values ('12345678989013293'),('asdvcmw1221323234'), ('!@#$&*92190247384dskdjdkfhdfh'), ('dskdjdkfhdfh')
    
    
    select String, 
    Case 
    (Case when String LIKE '%[0-9]%' and String Not LIKE '%[a-z]%' then 1 
    when String Not LIKE '%[0-9]%' and String LIKE '%[a-z]%' then 2 
    else 0 end 
    ) When 1 then 'NumericOnly'
    When 2 then 'AlphaOnly'
    else 'Both' end
    as AlphaNumbersOnly
    from @t 
    
    

    Please visit my Blog for some easy and often used t-sql scripts
    Monday, April 11, 2011 5:33 AM