Thursday, August 15, 2019

Defining Data Type

Have you ever had a problem to sort values in a VARCHAR column by a different data types?
All of them are stored as a text, how to check their data types?

Here is a simple query to solve that problem:

DECLARE @table TABLE (Unknown VARCHAR(100));

INSERT INTO @table VALUES
('0'),('9'),('-27'),('257'),('   20190815'),('7855567554354')
,('0.00000000000000000000000000000000000063')
,('34.349732949394273943654684095609489063')
,('2349347923437438976567456687685.1'),('6.3E-37')
,('2019-08-15'),('23:45:17'),('20190815 23:45:09')
,('62FD6025-7775-4F28-88EB-BCF9DE33D831')
,('0x5B612D7A412D5A5D5B612D7A412D5A5D25');

SELECT [Value] = Unknown
       , [BIT] = IIF(LEN(Unknown) = 1 and TRY_CAST(Unknown AS TINYINT) in (0,1),'x',Null)
       , [TINYINT] = IIF(TRY_CAST(Unknown AS TINYINT) Is Not Null,'x',Null)
       , [SMALLINT] = IIF(TRY_CAST(Unknown AS SMALLINT) Is Not Null,'x',Null)
       , [INT] = IIF(TRY_CAST(Unknown AS INT) Is Not Null,'x',Null)
       , [BIGINT] = IIF(TRY_CAST(Unknown AS BIGINT) Is Not Null,'x',Null)
       , [DECIMAL] = IIF(TRY_CAST(Unknown AS DECIMAL(38,0)) Is NOT Null,'x',Null)
       , [FLOAT] = IIF(TRY_CAST(Unknown AS FLOAT) Is NOT Null,'x',Null)
       , [BINARY] = CASE WHEN Unknown not like '0x%' OR LEN(Unknown) % 2 = 1 THEN NULL
                    ELSE IIF(Unknown LIKE '0x%[g-zG-Z]%', NULL, 'x' ) END
       , [DATETIME] = IIF(TRY_CAST(Unknown AS DATETIME) Is Not Null,'x',Null)
       , [UNIQUEIDENTIFIER] = IIF(TRY_CAST(Unknown AS UNIQUEIDENTIFIER) Is Not Null,'x',Null)
FROM @table;

GO

Here is the result:


No comments:

Post a Comment