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

 

Enthusiast

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

while converting the bytes to varchar its dropping a zero 

 

SELECT
FROM_BYTES('00'xb||SUBSTRING(cartid FROM 6 FOR 1),'base16'),

SUBSTRING(cartid FROM 6 FOR 1)
results in 
1    01

BYTES 5E-57-2B-1A-1C-01-DB-4C-94-71-7A-98-4A-BD-64-70

can you explain why this is happening 

 

Junior Contributor

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

Yep, it's dropping all leading zeroes, it's by design.

 

You can apply LPAD:

LPad(From_Bytes('00'xb||Substring(cartid From 6 FOR 1),'base16'),2,'0')

But because the resulting datatype of LPAD is a dumb VARCHAR(32000) CHARACTER SET UNICODE you might need to shorten it using CAST to avoid max row length errors:

Cast(LPad(From_Bytes('00'xb||Substring(cartid From 6 FOR 1),'base16'),2,'0') AS CHAR(2))

 

Enthusiast

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

wont i have to determine where there's 0 first before padding it .. if i pad it that means i am always adding zeros even if they dont exist..

Enthusiast

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

RETURN CAST(FROM_BYTES('00'xb||SUBSTRING(id FROM 4 FOR 1)||SUBSTRING(id FROM 3 FOR 1)||SUBSTRING(id FROM 2 FOR 1)||SUBSTRING(id FROM 1 FOR 1),'base16') AS VARCHAR(8))
||'-' ||CAST(FROM_BYTES('00'xb||SUBSTRING(id FROM 6 FOR 1)||SUBSTRING(id FROM 5 FOR 1),'base16') AS VARCHAR(4))
||'-' || CAST(FROM_BYTES('00'xb||SUBSTRING(id FROM 8 FOR 1)||SUBSTRING(id FROM 7 FOR 1),'base16') AS VARCHAR(4))
||'-' || CAST(FROM_BYTES('00'xb||SUBSTRING(id FROM 9 FOR 1)||SUBSTRING(id FROM 10 FOR 1),'base16') AS VARCHAR(4))
||'-' || CAST(FROM_BYTES('00'xb||SUBSTRING(id FROM 11),'base16') AS VARCHAR(12));

 

this is my function right now amd seems lke there will be padding 0 at every begining so 4,6,9 &11 position.. is that correct

Junior Contributor

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

LPad always returns exactly the number of characters specified, padded when there are less and truncated when more characters.

 

You need to add LPad to each FROM_BYTES:

Cast(LPad(From_Bytes('00'xb||Substring(messageid From  4 FOR 1)||Substring(messageid From  3 FOR 1)||Substring(messageid From 2 FOR 1)||Substring(messageid From 1 FOR 1),'base16'), 8, '0') AS VARCHAR(8)) ||'-' || 
Cast(LPad(From_Bytes('00'xb||Substring(messageid From  6 FOR 1)||Substring(messageid From  5 FOR 1),'base16'), 4, '0') AS VARCHAR(4)) ||'-' || 
Cast(LPad(From_Bytes('00'xb||Substring(messageid From  8 FOR 1)||Substring(messageid From  7 FOR 1),'base16'), 4, '0') AS VARCHAR(4)) ||'-' || 
Cast(LPad(From_Bytes('00'xb||Substring(messageid From  9 FOR 1)||Substring(messageid From 10 FOR 1),'base16'), 4, '0') AS VARCHAR(4)) ||'-' || 
Cast(LPad(From_Bytes('00'xb||Substring(messageid From 11),'base16'), 12, '0') AS VARCHAR(12))

More ugly code, another reason to implement a C-UDF :-)