user1874594 user1874594 - 3 months ago 21
SQL Question

Teradata SQL VARCHAR and spaces

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,,)


Here
( ,1243444,3455435,8768810,'fdff',5,7,8,,)
are
(someirrelvant_datatype,varchar(x),varchar(y),varchar(z),char(5),smallint,int,int,Timestamp)


So data looks like this

SEL CHAR2HEXINT ( colA ) ,colA FROM v


313732353732 172572 /* this is some other pre-existing value*/
2020202033343535343335 3455435 /* this is value just inserted */


So if you see above
20202020
Hex for
2 spaces aka ' '


2nd Question ( not continuation of above situation )

Also I have data for a varchar (50 ) col like this

HMSA
232434343
HMSA
4343434343
434343434


Here the
HMSA
has a trailing space
2020 HEX
but other values do not have
trailing spaces
. Not sure whats going on. Now here isn't
varchar
supposed to throw away all trailing spaces ? how come data landed with
HMSA<space>

Not sure . Any ideas ?

Ok so lets look at some oddities coming from this :


insert ( 'yada ') into tb /* tb is volatile & SET.There are 4 spaces */
insert ( 'yada') into tb

Query Failed. 2802: Duplicate row error in tb


Alright seeing what this looks like


7961646120202020 yada /*There are 4 spaces - 20 x 4 */


So on the 2nd insert it threw off the space comparison part and did a
trim(oldvalue) vs trim ( new value )

Answer

Q1: When you run a SELECT FORMAT(3455435), TYPE(3455435) you get -(10)9 INTEGER

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).