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

Database

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

I'm trying to migrate a column data (of type unique identifier) from SQL server to Teradata. So, I'm wondering whether there's a `uniqueidentifier` datatype equivalent in Teradata? If not, then the only option I think I've is to represent it as varchar(36).

 

12 REPLIES
N/A

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

There's no UUID datatype in Teradata, if you only want to store existing data you can use a BYTE(16).

 

If you need to create UUIDs you might code a C-UDF implementing it and a formatting function (or a User Defined Datatype)

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

we are converting Unique Identifer from SQl server and storing it as BYTE 16 in teradata. we are unable to get the string value that we see in sql Server

 

Unique Identifier 
4DE1327B-B086-4714-A289-54432100E50B

above value converted to Binary 16 on sql server 

0x7B32E14D86B01447A28954432100E50B

on teradata 

 

Column value on teradata stored as Byte(16) 
7B32E14D86B01447A28954432100E50B

 

now in teradata we trynig to convert the value to this string '4DE1327B-B086-4714-A289-54432100E50B'

we have used the below code but that hasnt helped 

FROM_BYTES(MessageId,'base16') 

 

Teradata Employee

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

I don't understand how you get from
4DE1327B-B086-4714-A289-54432100E50B
to
0x7B32E14D86B01447A28954432100E50B

The first three words are reversed but the others are not.  If this is supposed to represent a binary number or string, how can it be both big-endian and little-endian?

Maybe you should try just copying it over as Characters?

Highlighted
Teradata Employee

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

There is no built-in function to format a BYTE value in this way.

You could create a SQL UDF to do it, e.g. using SUBSTRING, FROM_BYTES, and concatentation.

 

FROM_BYTES(SUBSTRING(bytecol FROM 4 FOR 1)||SUBSTRING(bytecol FROM 3 FOR 1)||SUBSTRING(bytecol FROM 2 FOR 1)||SUBSTRING(bytecol FROM 1 FOR 1),'base16')

||'-'||FROM_BYTES...

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

the first column is unique identifier and then i convert it into binary(16)  and in third column i convert it back into unique identifier , use a different example in this one then above

 

SELECT messageid,
CONVERT(binary(16),messageid) ,
convert(uniqueidentifier,CONVERT(binary(16),messageid) )

68D339EB-2273-43A0-A2C5-2A7E05B0E134

0xEB39D3687322A043A2C52A7E05B0E134

68D339EB-2273-43A0-A2C5-2A7E05B0E134

 

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


Fred wrote:

There is no built-in function to format a BYTE value in this way.

You could create a SQL UDF to do it, e.g. using SUBSTRING, FROM_BYTES, and concatentation.

 

FROM_BYTES(SUBSTRING(bytecol FROM 4 FOR 1)||SUBSTRING(bytecol FROM 3 FOR 1)||SUBSTRING(bytecol FROM 2 FOR 1)||SUBSTRING(bytecol FROM 1 FOR 1),'base16')

||'-'||FROM_BYTES...


i tried this but didnt give me the result 

 

SyntaxEditor Code Snippet

SELECT messageid,CAST(FROM_BYTES(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') AS VARCHAR(10)) ||'-' || CAST(FROM_BYTES(SUBSTRING(messageid FROM 6 FOR 1)||SUBSTRING(messageid FROM 5 FOR 1),'base16') AS VARCHAR(10))||'-' || CAST(FROM_BYTES(SUBSTRING(messageid FROM 8 FOR 1)||SUBSTRING(messageid FROM 7 FOR 1),'base16') AS VARCHAR(10))||'-' || CAST(FROM_BYTES(SUBSTRING(messageid FROM 10 FOR 1)||SUBSTRING(messageid FROM 9 FOR 1),'base16') AS VARCHAR(10))||'-' || CAST(FROM_BYTES(SUBSTRING(messageid FROM 16 FOR 1)||SUBSTRING(messageid FROM 15 FOR 1)||SUBSTRING(messageid FROM 14 FOR 1)||SUBSTRING(messageid FROM 13 FOR 1),'base16') AS VARCHAR(10)) 

 EB-39-D3-68-73-22-A0-43-A2-C5-2A-7E-05-B0-E1-34

 

68D339EB-2273-43A0--3A5E-34E1B005

the expected value is 

68D339EB-2273-43A0-A2C5-2A7E05B0E134

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

Improvides a little more  the 9 ,10 and 16 character is whats not correct still 


SELECT messageid, CAST(FROM_BYTES(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') AS VARCHAR(10)) ||'-' || CAST(FROM_BYTES(SUBSTRING(messageid FROM 6 FOR 1)||SUBSTRING(messageid FROM 5 FOR 1),'base16') AS VARCHAR(10)) ||'-' || CAST(FROM_BYTES(SUBSTRING(messageid FROM 8 FOR 1)||SUBSTRING(messageid FROM 7 FOR 1),'base16') AS VARCHAR(10)) || CAST(FROM_BYTES(SUBSTRING(messageid FROM 9 FOR 1)||SUBSTRING(messageid FROM 10 FOR 1),'base16') AS VARCHAR(10)) ||'-' || CAST(FROM_BYTES(SUBSTRING(messageid FROM 11 FOR 1)||SUBSTRING(messageid FROM 12 FOR 1)||SUBSTRING(messageid FROM 13 FOR 1)||SUBSTRING(messageid FROM 14 FOR 1) ||SUBSTRING(messageid FROM 15 FOR 1) ||SUBSTRING(messageid FROM 16 FOR 1),'base16') AS VARCHAR(10))

 

EB-39-D3-68-73-22-A0-43-A2-C5-2A-7E-05-B0-E1-34	
68D339EB-2273-43A0-5D3B-2A7E05B0E1

 

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


GJColeman wrote:

I don't understand how you get from
4DE1327B-B086-4714-A289-54432100E50B
to
0x7B32E14D86B01447A28954432100E50B

The first three words are reversed but the others are not.  If this is supposed to represent a binary number or string, how can it be both big-endian and little-endian?

Maybe you should try just copying it over as Characters?


this is the article on UniqueIdentifier 

https://technet.microsoft.com/en-us/library/ms190215(v=sql.105).aspx

N/A

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

You truncate the last FromBytes to 10 characters (should be 12) and you should add a zero byte in front (to avoid problems with "negative" values). This should work:

Cast(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') AS VARCHAR(8)) ||'-' || 
Cast(From_Bytes('00'xb||Substring(messageid From  6 FOR 1)||Substring(messageid From  5 FOR 1),'base16') AS VARCHAR(4)) ||'-' || 
Cast(From_Bytes('00'xb||Substring(messageid From  8 FOR 1)||Substring(messageid From  7 FOR 1),'base16') AS VARCHAR(4)) ||'-' || 
Cast(From_Bytes('00'xb||Substring(messageid From  9 FOR 1)||Substring(messageid From 10 FOR 1),'base16') AS VARCHAR(4)) ||'-' || 
Cast(From_Bytes('00'xb||Substring(messageid From 11),'base16') AS VARCHAR(12))

 Of course best case would still be a C-UDF :-)