I have a attribute with the data type char(256). I import the value via SQL Developer from a csv file When the attribute gets a value with 10 characters, the remaining space gets filled with spaces.
I know that char allocates the space staticly, but does that also mean that I get a string in the format like "abc " ?
Since this make sql statements with equal operators difficult.
You are operating under a misconception; it has nothing to do with SQL Developer.
A CHAR data-type is a fixed-length string; if you do not provide a string of the full length then Oracle will right-pad the string with space (ASCII 32) characters until it has the correct length.
From the documentation:
CHARDatatypeThe
CHARdatatype stores fixed-length character strings. When you create a table with aCHARcolumn, you must specify a string length (in bytes or characters) between 1 and 2000 bytes for theCHARcolumn width. The default is 1 byte. Oracle then guarantees that:
- When you insert or update a row in the table, the value for the
CHARcolumn has the fixed length.- If you give a shorter value, then the value is blank-padded to the fixed length.
- If a value is too large, Oracle Database returns an error.
Oracle Database compares
CHARvalues using blank-padded comparison semantics.
To solve this, do not use CHAR for variable length strings and use VARCHAR2 instead.
VARCHAR2andVARCHARDatatypesThe
VARCHAR2datatype stores variable-length character strings. When you create a table with aVARCHAR2column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for theVARCHAR2column. For each row, Oracle Database stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case Oracle Database returns an error. UsingVARCHAR2andVARCHARsaves on space used by the table.
You may user varchar2 instead of char as datatype to avoid this.
Or you can trim your data in query by using rtrim(columnname) .
