Teradata: Byte Comparing
Following datatypes can be used to store 'Binary String Data'
- Byte(n) : It specifies a fixed length column of length 'n' for binary string data. The maximum value for 'n' is 64000.
- VarByte(n) : It specifies a varying length column of length 'n' for binary string data. The maximum value for 'n' is 64000.
Example: Binary String Data
00003b77
Sample Table : Creating a table with Byte data type
CREATE MULTISET VOLATILE TABLE byte_users
(
id INTEGER,
userId Byte(4)
)
ON COMMIT PRESERVE ROWS;
Data Population : Inserting couple of records in the table for testing query
INSERT INTO byte_users(1, '00003b77'XB);
INSERT INTO byte_users(2, '00007b23'XB);
Data View : Checking data in the byte_users table
SELECT * FROM byte_users;
Output:
id userId
1 00003b77
2 00007b23
Normal Querying 1: Trying to find data based on the byte characters but it will result in error.
SELECT * FROM byte_users WHERE userId='00003b77';
Output:
*** Failure 3640 Comparing BYTE data in column userId with other types is illegal.
Normal Querying 2: Trying to find data by casting characters to byte but it will result in error.
SELECT * FROM byte_users WHERE userId=CAST('00003b77' AS BYTE(4));
Output:
*** Failure 3532 Conversion between BYTE data and other types is illegal.
How to query columns containing byte data?
Approach: Suffix XB at the end of the string,For example
SELECT * FROM byte_users WHERE userId='00003b77'XB;
Output:
id userId
1 00003b77
What does 'XB' denotes which is used in the query?
'x' indicates that the value of literal is hexadecimal and 'b' indicates that the datatype is byte.