in a table i have a column wich contain both alpha and numeric values as rows, i want to know which row contain alpha values ,wich row contain numeric values and wich row contain both alphanumeric values.

Rupa 1 Reputation point

Hi team,

I want to know alpha and numeric values of each row in a column,

Without using patindex

Source data:




A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,596 questions
{count} votes

2 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,181 Reputation points

    Hi @Rupa ,

    Please refer to below :

    declare @table table  
    ([column] varchar(100))  
    insert into @table values  
    select [column],CASE   
    WHEN ISNUMERIC([column])=1 THEN 'numberic'  
    WHEN [column] LIKE '%[A-Z]%' and [column] not LIKE '%[0-9]%' THEN 'alpha'  
    WHEN [column] LIKE '%[A-Z]%' and [column] LIKE '%[0-9]%' THEN 'alphanumberic'   
    END AS output   
    from @table  


    column output  
    Abc alpha  
    123 numberic  
    Abc123 alphanumberic  
    456 numberic  
    45adb alphanumberic  

    Best regards,

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Erland Sommarskog 105.9K Reputation points MVP

    Using Melissa's test data:

    declare @table table
     ([column] varchar(100))
     insert into @table values
     SELECT [column], 
            CASE WHEN "column" NOT LIKE '%[^0-9]%' THEN 'Numeric'
                 WHEN "column" NOT LIKE '%[^aA-Z]%' THEN 'Alphaonly'
                 WHEN "column" NOT LIKE '%[^aA-Z0-9]%' THEN 'Alphanum'
                 ELSE 'others'
    FROM  @table
    0 comments No comments