SP or func to determine a suitable datatype

• BrenBart

  SSCrazy

  Points: 2622

  More actions

  January 19, 2004 at 8:43 am

  #107360

  Does anyone know if there is a system function or stored procedure that given a value it will return a suitable datatype?

  i.e. given 'asdfasdfasdf' it will return a char(12)

  '234' returns an int

  '234.566' returns a numeric or decimal

  Anything like that would be good.

  
  "I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
  (Memoirs of a geek)
  

• Calvin Lawson

  SSChampion

  Points: 11030

  More actions

  January 21, 2004 at 7:43 pm

  #491061

  Not that I know of, but you could probably make one. 

  Something like below should do the trick:

   

  select Value,

   Case

    When isnumeric(Value)= 0 and isdate(Value) = 0  then 'char(' + cast(len(Value) as varchar) + ')'

    When isnumeric(Value)=1 and charindex('.', Value) = 0  then 'int'

    When isnumeric(Value)=1 and charindex('.', Value) <> 0 then 'numeric'

    When isdate(Value)=1 then 'date'

   End

  from #Val

   

  Signature is NULL