3

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.

MT0
  • 143,790
  • 11
  • 59
  • 117
J. Doe
  • 522
  • 6
  • 22

2 Answers2

6

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:

CHAR Datatype

The CHAR datatype stores fixed-length character strings. When you create a table with a CHAR column, you must specify a string length (in bytes or characters) between 1 and 2000 bytes for the CHAR column width. The default is 1 byte. Oracle then guarantees that:

  • When you insert or update a row in the table, the value for the CHAR column 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 CHAR values using blank-padded comparison semantics.

To solve this, do not use CHAR for variable length strings and use VARCHAR2 instead.

VARCHAR2 and VARCHAR Datatypes

The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. 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. Using VARCHAR2 and VARCHAR saves on space used by the table.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • SO I should always use VARCHAR2 and if I know for sure the length of all entries is the same I could use char for better performance? – J. Doe Jan 17 '21 at 16:14
3

You may use varchar2 instead of char as datatype to avoid this.

Or you can trim your data in query by using rtrim(columnname) .

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
  • SO I should always use VARCHAR2 and if I know for sure the length of all entries is the same I could use char for better performance? – J. Doe Jan 17 '21 at 16:14
  • Yes you should only use CHAR if all of the records are of same size. Hope your problem has been resolved. If you need to retrieve data from any CHAR field and you don't know for sure whether their size varies or not you can trim all the blank spaces from the right side with RTRIM() or RPAD(). – Kazi Mohammad Ali Nur Romel Jan 17 '21 at 16:29