insert command - missed space char

[Former Member]

Hello all,

i try insert string with space char at end, for example:

create table t1 ( f1 varchar (10) )

//

insert into t1 values ('a ')

//

select f1 & 'x' from t1

and query displays 'ax' not 'a x' (probably a bug).

How can I avoid this? I can not delete space(s) at end.

Regards,

Lukasz

MaxDB 7.6.03.07 WinXP SP2.

[lbreddemann · Accepted solution]

Hi Lukasz,

this is a feature not a bug

MaxDB trims the spaces at the end of characterstrings before inserting them into the table.

Lookup the documentation for "Data Type of the Target Column and Inserted Value". There you'll find this (<a href="http://maxdb.sap.com/currentdoc/a0/8fa5e82d2e11d3a98100a0c9449261/content.htm">Link2Doku</a>):

<i><b>Target column C</b>

Alphanumeric column with the code attribute ASCII, UNICODE

<b>Required value for v</b>

Character string whose length is not greater than the length attribute of C. Subsequent blanks are ignored when the length of v is calculated. If the length of v is shorter than the length attribute of C, then v is lengthened by the corresponding number of blanks.</i>

You can also check it for UPDATE on character columns.

Try it out e.g. with the length function:

select f1, length(f1) from t1

In most cases this saves space as most often whitespace has no specific meaning in data. If you really need the spaces preserved one option would be to add a character at the end of the string and remove it again when you use the data in your application.

Best regards,

Lars