1

Given:

  • A table tA with an "ID" and a "Description" columns
  • "Description" is a string column.

  • The contents of the table are:

    ID || Description
    1 || "String1"
    2 || "String2"
    3 || "String3"

If I execute the following SQL query:
"SELECT ID FROM tA WHERE Description = 'String2'" it returns 2 (as expected)

But:

If I execute the following SQL query:
"SELECT ID FROM tA WHERE Description = 'String2 '" (trailing withespaces) it also returns 2! (as it is an exact comparison, it should return NONE)

If I execute the following SQL query:
"SELECT ID FROM tA WHERE Description = ' String2'" (leading withespaces) it returns NONE (as expected)

Do you know what is the reason of this difference in behaviour?

Thanks in advance.

matmat
  • 85
  • 9
  • 1
    I cant speak for Cache but this is a common behaviour in databases mandated in the SQL92 standard: *pad strings before comparing so they have the same length* - in general this means trailing white space is ignored, where as leading white space is not. (presumably this was done to ease working with fixed width char types) – Alex K. Jul 30 '15 at 11:45

1 Answers1

1

You need to use "%EXACT" around your column name. This should return no records: "SELECT ID FROM tA WHERE %EXACT(Description) = 'String2 '"

joebeeson
  • 4,159
  • 1
  • 22
  • 29