how to get the data type of each row in a column-CodePudding

I need to know the datatype of eachrow in a column.

Source:

enter image description here

output:

enter image description here

Thanks In Advance

CodePudding user response：

SELECT *
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'MySchema' -- default is dbo
  AND  TABLE_NAME = 'MyTable'

CodePudding user response：

Using try_convert() in concert with a CASE (or two)

Declare @YourTable Table ([column] varchar(50))
Insert Into @YourTable Values 
 ('Abc')
,('123')
,('Adc123')
,('456')
,('45adb')
,('25.25')
 
Select *
      ,[output]  = case when try_convert(money,[column]) is null 
                        then 'alpha'   case when patindex('%[0-9]%',[column])>0 then 'numeric' else '' end
                        else 'numeric' end
 From @YourTable

Results

column  output
Abc     alpha
123     numeric
Adc123  alphanumeric
456     numeric
45adb   alphanumeric
25.25   numeric

Note: I use money because it tends to be a little more forgiving.