Does Teradata equality condition on select query doesnt consider right spaces?

Database
Teradata Employee

Does Teradata equality condition on select query doesnt consider right spaces?

Hi Forum,

 

Does Teradata equality condition on select query doesnt consider right spaces?

 

BTEQ -- Enter your SQL request or BTEQ command:
create table test(a varchar(20),b int);

create table test(a varchar(20),b int);

*** Table has been created.
*** Total elapsed time was 1 second.


BTEQ -- Enter your SQL request or BTEQ command:
show table test;

show table test;

*** Text of DDL statement returned.
*** Total elapsed time was 1 second.

---------------------------------------------------------------------------
CREATE SET TABLE SYSTEMFE.test ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
a VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
b INTEGER)
PRIMARY INDEX ( a );


BTEQ -- Enter your SQL request or BTEQ command:
insert into test('abc    ',2);

insert into test('abc    ',2);

*** Insert completed. One row added.
*** Total elapsed time was 1 second.


BTEQ -- Enter your SQL request or BTEQ command:
select character_length(a),a,b from test;

select character_length(a),a,b from test;

*** Query completed. One row found. 3 columns returned.
*** Total elapsed time was 1 second.

Characters(a) a b
------------- -------------------- -----------
7 abc 2

BTEQ -- Enter your SQL request or BTEQ command:
select * from test where a='abc';

select * from test where a='abc';

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

a b
-------------------- -----------
abc 2

BTEQ -- Enter your SQL request or BTEQ command:
select * from test where a='abc    ';

select * from test where a='abc    ';

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

a b
-------------------- -----------
abc 2

BTEQ -- Enter your SQL request or BTEQ command:
select * from test where a='abc ';

select * from test where a='abc ';

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

a b
-------------------- -----------
abc 2


BTEQ -- Enter your SQL request or BTEQ command:
select * from test where a='abc' (casespecific);

select * from test where a='abc' (casespecific);

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

a b
-------------------- -----------
abc 2

BTEQ -- Enter your SQL request or BTEQ command:

 

Please advise.

 

Best Regards,

Sandeep.

 

GANGA SANDEEP KUMAR

1 REPLY 1
Highlighted
Ambassador

Re: Does Teradata equality condition on select query doesnt consider right spaces?

You probably worked on Oracle before where this is not implemented correctly (similar to '' being equal to NULL).

Oracle treats 'abc' and 'abc ' as two different values while Standard SQL are different: if two strings are compared the shorter one is padded with spaces to the size of the longer one.

 

If you want to find an exact match you can apply LIKE without wildcard (comparison rules are different for LIKE):

select * from test where a LIKE 'abc ';