CODEWITHSUNDEEP
sqldb2
Kishore_2021
Kishore_2021

Reputation: 689

DB2 SQL Query not considering spaces in field's data

The database in my application saves data in table's field with spaces. But when I query the data it's not considering spaces in field's data And gives the all result irrespective the spaces

VAT_TABLE.VAR_DATA
------------------
I                   --- 1 space after I
I                   --- 2 space after I
I                   --- 0 space after I
I                   --- 1 space after I
I                   --- 3 space after I
I                   --- 4 space after I

When I run this query >>> SELECT * FROM VAT_TABLE WHERE VAR_DATA = 'I '

The result gives all 6 rows irrespective the spaces after 'I'

Any Idea??

Upvotes: 0

Views: 2538

Answers (1)

James Anderson
James Anderson

Reputation: 27478

DB2 specifically ignores trailing spaces when comparing two strings.

So "I" == "I " == "I "

If you want to differentiate between "I" and "I " you need :-

WHERE VAR_DATA = 'I' AND LENGTH(VAR_DATA) = 2

This behavior reflects the comparison logic of COBOL, MVS Assembler and PL/1 which were the dominant languages on the target platforms when DB2 was originally developed.

Upvotes: 1

Related Questions