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.

Question

Hi team,

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

Without using patindex

Source data:

output:

Answer 1

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.

Answer 2

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