I have a table in Oracle Database that its column type is char(100).
| ID | VALUE |
|---|---|
| 1 | Test |
In insertion the 'Test' value is blank padded by oracle. When I select this row from the database I must trim it to get the non-padded value.
select trim(value) from mytable where id = 1
It does not make sense cause it is not the actual inserted value. Is there any solution to get the actual non-padded value without using trim or changing the column type to varchar ?
The short answer is "no" there isn't a way to do this. The CHAR datatype automatically pads the value you insert to the full size of the column. In my experience, CHAR is rarely used because it takes up disk space and inflates storage requirements, even for empty values. If you want only your data to be stored without padding, you must use VARCHAR2.
See here: https://www.oracletutorial.com/oracle-basics/oracle-char/
When you insert or update a fixed-length character string column, Oracle stores the characters as the fixed-length data.
It means that if you store a value whose length is less than the maximum length defined in the column, Oracle pads the spaces to the character string up to the maximum length.