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: