Difference in Inserting '1 ' and '1'

Database
Kde
Enthusiast

Difference in Inserting '1 ' and '1'

Hi All,

I have very basic doubt. what is difference between inserting '1 ' and '1' in Teradata.

CREATE SET TABLE FINANCIAL.VAR ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
acct_nbr VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( acct_nbr );

INSERT INTO VAR VALUES ('1 ');
INSERT INTO VAR VALUES ('1');

When i execute second DML ,it throw duplicate error .

My doubt is
SELECT chars('1 ') ; will give 3
SELECT CHARS('1') ; will give 1 , so actually they are different value but why it is giving duplicate key error.

Just tried inserting the same value in oracle which is defined as primary key ,it allows(Should not compare oracle and teradata but out of curiosity i checked)

Thanks

4 REPLIES
Enthusiast

Re: Difference in Inserting '1 ' and '1'

Both queries
select hashrow('1');
select hashrow('1 ');

returns the same 4C-6C-48-9E

May be some bag...
Junior Contributor

Re: Difference in Inserting '1 ' and '1'

It's not a bug, this is how the algorithm works, trailing blanks are removed for hashing.

Dieter
Enthusiast

Re: Difference in Inserting '1 ' and '1'

More generally, trailing blanks are not significant in comparisons of character string values. That is required for ANSI/ISO standard SQL conformance.
Kde
Enthusiast

Re: Difference in Inserting '1 ' and '1'

Thanks a lot for clarrification..