Why pad instead of trim when comparing strings?

Question

The ANSI/SQL-92 standard states that strings have to be padded to the same length when compared:

     3) The comparison of two character strings is determined as fol-
        lows:

        a) If the length in characters of X is not equal to the length
          in characters of Y, then the shorter string is effectively
          replaced, for the purposes of comparison, with a copy of
          itself that has been extended to the length of the longer
          string by concatenation on the right of one or more pad char-
          acters, where the pad character is chosen based on CS. If
          CS has the NO PAD attribute, then the pad character is an
          implementation-dependent character different from any char-
          acter in the character set of X and Y that collates less
          than any string under CS. Otherwise, the pad character is a
          <space>.

        b) The result of the comparison of X and Y is given by the col-
          lating sequence CS.

        c) Depending on the collating sequence, two strings may com-
          pare as equal even if they are of different lengths or con-
          tain different sequences of characters. When the operations
          MAX, MIN, DISTINCT, references to a grouping column, and the
          UNION, EXCEPT, and INTERSECT operators refer to character
          strings, the specific value selected by these operations from
          a set of such equal values is implementation-dependent.

        Note: If the coercibility attribute of the comparison is
        Coercible, then the collating sequence used is the default de-
        fined for the character repertoire. See also other Syntax Rules
        in this Subclause, Subclause 10.4, "<character set specifi-
        cation>", and Subclause 11.28, "<character set definition>".

Other than the obvious "it isn't in the standard", what would be the reasons against trimming the ends of the strings instead of padding them to the same length?

I'm trying to find if there's any reason I should implement padding instead of just solving the problem via a simple right trim of white space and what is the case where this would make me non ANSI compliant if the end result is the same. — Vedran, Jun 07 '19 at 10:58

score 2 · Answer 1 · answered Jun 07 '19 at 08:20

2

It is no problem to trim the strings before comparing them.

The SQL standard does not describe the implementation, it describes the semantics of the comparison. It just finds it simpler to describe the behavior using padding.

You are free to implement the comparison any way you want as long as the behavior is identical.

answered Jun 07 '19 at 08:20

Laurenz Albe

51,298
4
39
69

1

Some time in the distant past databases used only fixed width columns and so the padding semantic got into the SQL standard. – Jasen Jun 07 '19 at 11:08

Why pad instead of trim when comparing strings?

1 Answers1