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
2021-10-01T03:48:31.597+00:00

Hi team,

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

Without using patindex

Source data:

136818-image.png

output:

136827-image.png

Transact-SQL
Transact-SQL
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
    2021-10-01T04:02:46.29+00:00

    Hi @Rupa ,

    Please refer to below :

    declare @table table  
    ([column] varchar(100))  
      
    insert into @table values  
    ('Abc'),  
    ('123'),  
    ('Abc123'),  
    ('456'),  
    ('45adb')  
      
    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  
    

    Output:

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

    Best regards,
    Melissa


    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
    2021-10-01T22:06:28.673+00:00

    Using Melissa's test data:

    declare @table table
     ([column] varchar(100))
    
     insert into @table values
     ('Abc'),
     ('123'),
     ('Abc123'),
     ('456'),
     ('45adb')
    
     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'
            END
    FROM  @table
    
    0 comments No comments