CODEWITHSUNDEEP
sqldatabaseteradatavarcharsqldatatypes
user1874594
user1874594

Reputation: 2483

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 )

Upvotes: 2

Views: 3130

Answers (1)

dnoeth
dnoeth

Reputation: 60482

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

Upvotes: 7

Related Questions