Is there a uniqueidentifier data type (in SQL server) equivalent in Teradata?

Database
Enthusiast

Re: Is there a uniqueidentifier data type (in SQL server) equivalent in Teradata?

That does work ... i agree C UDF would certainly be better... maybe Teradata can add some kind of support for unique  identifier data type :) 

Enthusiast

Re: Is there a uniqueidentifier data type (in SQL server) equivalent in Teradata?

now i am trying to do the reverse, convert the unique identifier string into byte(16), its apppending a 00 , is there a way arounf that 

 

SyntaxEditor Code Snippet

SELECT  'EB39D3687322A043A2C52A7E05B0E134',TO_BYTES(( SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 7 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 5 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 3 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 1 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 12 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 10 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 17 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 15 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 20 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 22 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 25 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 27 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 29 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 31 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 33 FOR 2)||SUBSTRING( CAST( '68D339EB-2273-43A0-A2C5-2A7E05B0E134' AS VARCHAR(36)) FROM 35 FOR 2)
) ,'base16')
Highlighted
Junior Contributor

Re: Is there a uniqueidentifier data type (in SQL server) equivalent in Teradata?

Your code can be simplified (only the first three groups are reversed):

 

To_Bytes(  Substring( Cast( guid AS VARCHAR(36)) FROM  7 FOR 2)
         ||Substring( Cast( guid AS VARCHAR(36)) FROM  5 FOR 2)
         ||Substring( Cast( guid AS VARCHAR(36)) FROM  3 FOR 2)
         ||Substring( Cast( guid AS VARCHAR(36)) FROM  1 FOR 2)
         ||Substring( Cast( guid AS VARCHAR(36)) FROM 12 FOR 2)
         ||Substring( Cast( guid AS VARCHAR(36)) FROM 10 FOR 2)
         ||Substring( Cast( guid AS VARCHAR(36)) FROM 17 FOR 2)
         ||Substring( Cast( guid AS VARCHAR(36)) FROM 15 FOR 2)
         ||Substring( Cast( guid AS VARCHAR(36)) FROM 20 FOR 4)
         ||Substring( Cast( guid AS VARCHAR(36)) FROM 25 ) ,'base16')

 

 

 

I don't know if it's more efficient but a Regular Expression can be used instead of all the Substrings:

 

To_Bytes(RegExp_Replace (guid,'^(..)(..)(..)(..)(?:-)(..)(..)(?:-)(..)(..)(?:-)(....)(?:-)','\4\3\2\1\6\5\8\7\9') ,'base16')

 

 

To get rid of the leading '00' you can apply another substring on the result:

 

Substring(
To_Bytes( Substring( Cast( guid AS VARCHAR(36)) FROM 7 FOR 2) ||Substring( Cast( guid AS VARCHAR(36)) FROM 5 FOR 2) ||Substring( Cast( guid AS VARCHAR(36)) FROM 3 FOR 2) ||Substring( Cast( guid AS VARCHAR(36)) FROM 1 FOR 2) ||Substring( Cast( guid AS VARCHAR(36)) FROM 12 FOR 2) ||Substring( Cast( guid AS VARCHAR(36)) FROM 10 FOR 2) ||Substring( Cast( guid AS VARCHAR(36)) FROM 17 FOR 2) ||Substring( Cast( guid AS VARCHAR(36)) FROM 15 FOR 2) ||Substring( Cast( guid AS VARCHAR(36)) FROM 20 FOR 4) ||Substring( Cast( guid AS VARCHAR(36)) FROM 25 ) ,'base16') FROM CASE WHEN Substring(guid From 7 FOR 1) > '7' THEN 2 ELSE 1 end)

 

Btw, if you're not ordering by the GUIDs you don't have to care about the internal representation and might simply store them as-is: 

 

'EB39D368-7322-A043-A2C5-2A7E05B0E134' <-> 'EB39D3687322A043A2C52A7E05B0E134'xb