user1874594 user1874594 - 1 year ago 156
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,,)

( ,1243444,3455435,8768810,'fdff',5,7,8,,)

So data looks like this


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

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

2nd Question ( not continuation of above situation )

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


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

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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download