Hsqldb - how to remove the padding on char fields

Question

I'm finding that Char fields are being padded.

Is there any way to stop this happening.

I've tried using the property

SET PROPERTY "sql.enforce_strict_size" FALSE

but doesn't seem to help.

Answer 1

I thought 'char' by definition are space padded to fill the field. They are considered fixed lenght and will be space padded to be fixed length.

The data type 'varchar' is defined as variable char where they are not space padded to fill the field.

I could be wrong though since I normally work on SQL Server.

Answer 2

Indeed, the MySQL docs specify that "When CHAR values are retrieved, trailing spaces are removed." This is odd, as other databases seem to always keep the padding (i can confirm that for Oracle). The SQL-92 standard indicates that right-padded spaces are part of the char, for example in the definition of the CAST function on p. 148. When source (SV=source value) and target (TV=target value, LTD=length of target datatype), then:

ii)  If the length in characters of SV is larger than LTD, then
     TV is the first LTD characters of SV. If any of the re-
     maining characters of SV are non-<space> characters, then a
     completion condition is raised: warning-string data, right
     truncation.

iii) If the length in characters M of SV is smaller than LTD,
     then TV is SV extended on the right by LTD-M <space>s.

Maybe that's just another one of MySQL's many oddities and gotchas.

And to answer your question: if you don't want the trailing spaces, you should use VARCHAR instead.