I dont quite follow when I do a plain insert of numeric data into a varchar
field why does it Left pad it with 2 spaces . Is not varchar supposed to trim off spaces ( trailing ok...but where is the rule that you "frontpad" with spaces) . Why then pad with 2 leading spaces
INSERT INTO v VALUES ( ,1243444,3455435,8768810,'fdff',5,7,8,,)
SEL CHAR2HEXINT ( colA ) ,colA FROM v
313732353732 172572 /* this is some other pre-existing value*/
2020202033343535343335 3455435 /* this is value just inserted */
2 spaces aka ' '
insert ( 'yada ') into tb /* tb is volatile & SET.There are 4 spaces */
insert ( 'yada') into tb
Query Failed. 2802: Duplicate row error in tb
7961646120202020 yada /*There are 4 spaces - 20 x 4 */
trim(oldvalue) vs trim ( new value )
Q1: When you run a
SELECT FORMAT(3455435), TYPE(3455435) you get
Every datatype always got a Cobol-style format used for casting to and from a string. In your case the datatypes of source and target didn't match and Teradata did an automatic type cast. The Format of an Integer is up to ten digits plus a leading sign right aligend.
Explixit type cast using Standard SQL
CAST apply no format and thus no leading spaces. So simply do
CAST(3455435 AS VARCHAR(20) or (TRIM(3455435)` instead.
Q2: A VarChar stores exactly what you insert, i.e.
'1234 ' will be stored including two spaces. And based on Standard SQL comparison rules trailing spaces are ignored when comparing strings, thus
'yada ' and
'yada' are considered equal (you probably worked with Oracle before which doesn't follow Standard SQL in this case).