-4

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

Source:

enter image description here

output:

enter image description here

Thanks In Advance

Thom A
  • 88,727
  • 11
  • 45
  • 75
swarupa kommalapati
  • 3
  • 2
  • You mean the data type of each column in a table, it will be the same for all rows. – Stu Sep 29 '21 at 15:55
  • Rows don't have data types, columns do. **Every** value in a single column all have the same data type. For your data, in a SQL table, every value would be an `(n)(var)char`. – Thom A Sep 29 '21 at 16:10
  • Interesting, because I didn't vote this as a dupe, but as unclear, it makes it *look* like I voted it as a dupe but doesn't put my gold badge next to it (as that only happens when I dupe). That looks.... Odd. – Thom A Sep 29 '21 at 16:15
  • Clearly the column is a string, but it looks like OP is looking to determine/refine the type. The dupe link is not appropriate. – John Cappelletti Sep 29 '21 at 16:28
  • I don't disagree that it's not a dupe, @JohnCappelletti, (and why I explicitly stated I didn't dupe vote) but I disagree with the reopen. It's as clear as mud. "Data types" of a row make no sense; a row doesn't have a data type a *column* does. And, of the "data types" they've listed, only `numeric` is an actual data type. – Thom A Sep 29 '21 at 22:04
  • Reading the *textual content* of the question, it sounds like the OP is clearly asking for X when in fact they mean Y - the question probably should not have any answers but more questions to seek clarification rather than relying on graphical representations of data - which I confess I did not look at when they were links, nor should I need to to understand the question. – Stu Sep 29 '21 at 22:17
  • Certainly a lazy question and poor phrasing, but the intent seemed very clear to me – John Cappelletti Sep 29 '21 at 22:18

2 Answers2

0

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.

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
-1
SELECT *
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'MySchema' -- default is dbo
  AND  TABLE_NAME = 'MyTable'
SQLpro
  • 3,994
  • 1
  • 6
  • 14