Comparing string values without space and with space: Why both are treated as same?

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Comparing string values without space and with space: Why both are treated as same?

I came across scenario where the result seems unexpected. Teradata is treating values 'FILLER VALUES WITH SPACES CHECK                                                                     ' and 'FILLER VALUES WITH SPACES CHECK' as same, ideally those should not as these are different values. Is it expected? Please help

Following can be used to recreate scenario:
/*

NOTE:
1. Both VT are same in definition
2. VT_FILLERVALUES_CHK_WITH_FILLER table values for column FILLERVALUES_CHK are with filler spaces appended
3. VT_FILLERVALUES_CHK_WITHOUT_FILLER table values for column FILLERVALUES_CHK are without filler spaces

 */

CREATE MULTISET VOLATILE TABLE VT_FILLERVALUES_CHK_WITH_FILLER
(
PI_RNK INTEGER,
FILLERVALUES_CHK VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC
)
PRIMARY INDEX ( PI_RNK )
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_FILLERVALUES_CHK_WITH_FILLER VALUES (1,'FILLER VALUES WITH SPACES CHECK                                                                     ');
INSERT INTO VT_FILLERVALUES_CHK_WITH_FILLER VALUES (2,'FILLER VALUES WITH SPACES CHECK                                                                     ');
INSERT INTO VT_FILLERVALUES_CHK_WITH_FILLER VALUES (3,'FILLER VALUES WITH SPACES CHECK                                                                     ');
INSERT INTO VT_FILLERVALUES_CHK_WITH_FILLER VALUES (4,'FILLER VALUES WITH SPACES CHECK                                                                     ');
INSERT INTO VT_FILLERVALUES_CHK_WITH_FILLER VALUES (5,'FILLER VALUES WITH SPACES CHECK                                                                     ');
INSERT INTO VT_FILLERVALUES_CHK_WITH_FILLER VALUES (6,'FILLER VALUES WITH SPACES CHECK                                                                     ');
INSERT INTO VT_FILLERVALUES_CHK_WITH_FILLER VALUES (7,'FILLER VALUES WITH SPACES CHECK                                                                     ');
INSERT INTO VT_FILLERVALUES_CHK_WITH_FILLER VALUES (8,'FILLER VALUES WITH SPACES CHECK                                                                     ');
INSERT INTO VT_FILLERVALUES_CHK_WITH_FILLER VALUES (9,'FILLER VALUES WITH SPACES CHECK                                                                     ');
INSERT INTO VT_FILLERVALUES_CHK_WITH_FILLER VALUES (10,'FILLER VALUES WITH SPACES CHECK                                                                     ');

 


CREATE MULTISET VOLATILE TABLE VT_FILLERVALUES_CHK_WITHOUT_FILLER
(
PI_RNK INTEGER,
FILLERVALUES_CHK VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC
)
PRIMARY INDEX ( PI_RNK )
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_FILLERVALUES_CHK_WITHOUT_FILLER VALUES (1,'FILLER VALUES WITH SPACES CHECK');
INSERT INTO VT_FILLERVALUES_CHK_WITHOUT_FILLER VALUES (2,'FILLER VALUES WITH SPACES CHECK');
INSERT INTO VT_FILLERVALUES_CHK_WITHOUT_FILLER VALUES (3,'FILLER VALUES WITH SPACES CHECK');
INSERT INTO VT_FILLERVALUES_CHK_WITHOUT_FILLER VALUES (4,'FILLER VALUES WITH SPACES CHECK');
INSERT INTO VT_FILLERVALUES_CHK_WITHOUT_FILLER VALUES (5,'FILLER VALUES WITH SPACES CHECK');
INSERT INTO VT_FILLERVALUES_CHK_WITHOUT_FILLER VALUES (6,'FILLER VALUES WITH SPACES CHECK');
INSERT INTO VT_FILLERVALUES_CHK_WITHOUT_FILLER VALUES (7,'FILLER VALUES WITH SPACES CHECK');
INSERT INTO VT_FILLERVALUES_CHK_WITHOUT_FILLER VALUES (8,'FILLER VALUES WITH SPACES CHECK');
INSERT INTO VT_FILLERVALUES_CHK_WITHOUT_FILLER VALUES (9,'FILLER VALUES WITH SPACES CHECK');
INSERT INTO VT_FILLERVALUES_CHK_WITHOUT_FILLER VALUES (10,'FILLER VALUES WITH SPACES CHECK');

/*
FOLLOWING IS TO SHOW THAT SPACES ARE ALSO STORED AS DATA IN TABLE
IF THESE WERE PERMANENT TABLES, YOU WOULD HAVE SEEN THE DIFFERENT IN TABLE SIZE (DBC.TABLESIZEV)
*/

SELECT FILLERVALUES_CHK
,LENGTH(FILLERVALUES_CHK) AS LEN_FILLERVALUES_CHK
FROM VT_FILLERVALUES_CHK_WITH_FILLER;

SELECT FILLERVALUES_CHK
,LENGTH(FILLERVALUES_CHK) AS LEN_FILLERVALUES_CHK
FROM VT_FILLERVALUES_CHK_WITHOUT_FILLER;


/*
ALL VALUES IN VT_FILLERVALUES_CHK_WITHOUT_FILLER ARE WITHOUT FILLER SPACE BUT VALUES IN VT_FILLERVALUES_CHK_WITH_FILLER ARE WITH FILLER SPACES
WHY TERADATA IS TREATING THESE VALUES AS SAME
*/

SELECT
FILLERVALUES_CHK
FROM VT_FILLERVALUES_CHK_WITH_FILLER
where FILLERVALUES_CHK = ( select FILLERVALUES_CHK from VT_FILLERVALUES_CHK_WITHOUT_FILLER where PI_RNK =1 group by 1);

SELECT
FILLERVALUES_CHK
FROM VT_FILLERVALUES_CHK_WITH_FILLER
where FILLERVALUES_CHK ='FILLER VALUES WITH SPACES CHECK';

/*
AGAIN SAME
*/

SELECT
FILLERVALUES_CHK
FROM VT_FILLERVALUES_CHK_WITHOUT_FILLER
where FILLERVALUES_CHK = ( select FILLERVALUES_CHK from VT_FILLERVALUES_CHK_WITH_FILLER where PI_RNK =1 group by 1);


SELECT
FILLERVALUES_CHK
FROM VT_FILLERVALUES_CHK_WITHOUT_FILLER
where FILLERVALUES_CHK = 'FILLER VALUES WITH SPACES CHECK ';

/*
AGAIN SAME - WITH JOINS
*/

SELECT
A.FILLERVALUES_CHK
,B.FILLERVALUES_CHK
,LENGTH(A.FILLERVALUES_CHK) AS LEN_WITH_FILLERS
,LENGTH(B.FILLERVALUES_CHK) AS LEN_WITHOUT_FILLER
FROM VT_FILLERVALUES_CHK_WITH_FILLER A
INNER JOIN VT_FILLERVALUES_CHK_WITHOUT_FILLER B
ON A. PI_RNK = B. PI_RNK
WHERE A.FILLERVALUES_CHK = B.FILLERVALUES_CHK;

/*
AND AGAIN
*/

SELECT CASE WHEN
'FILLER VALUES WITH SPACES CHECK                                                                     ' ='FILLER VALUES WITH SPACES CHECK'
THEN 'MATCHING' ELSE 'NOT MATCHING' END AS RES;


Accepted Solutions
Junior Contributor

Re: Comparing string values without space and with space: Why both are treated as same?

Of course this is expected did you work with Oracle before?), it's based on Standard SQL rules: when two strings with different length are compared the shorter one is padded with blanks to the size of the larger one.

 

Most human beings don't want 'a' and 'a ' to be different, it's hard to spot the spaces :-)

 

If you want to treat them different you can compare them using LIKE, which doesn't pad:

 FILLERVALUES_CHK = 'FILLER VALUES WITH SPACES CHECK ';

 

1 ACCEPTED SOLUTION
3 REPLIES
Highlighted
Enthusiast

Re: Comparing string values without space and with space: Why both are treated as same?

Checked with BTEQ, Permanent tables, Unicode -Unicode, Unicode-Latin, results are same

Junior Contributor

Re: Comparing string values without space and with space: Why both are treated as same?

Of course this is expected did you work with Oracle before?), it's based on Standard SQL rules: when two strings with different length are compared the shorter one is padded with blanks to the size of the larger one.

 

Most human beings don't want 'a' and 'a ' to be different, it's hard to spot the spaces :-)

 

If you want to treat them different you can compare them using LIKE, which doesn't pad:

 FILLERVALUES_CHK = 'FILLER VALUES WITH SPACES CHECK ';

 

Enthusiast

Re: Comparing string values without space and with space: Why both are treated as same?

Good to know that :)

 

Thanks a lot