[SOLVED] Why does CAST truncate text instead of throwing an error?

Why does CAST truncate text instead of throwing an error?

Casting text to a length that is shorter than the value results in truncated text.

select
    cast('ROAD-1234' as varchar2(8)) as id
from
    dual

ID      
--------
ROAD-123     
     --^ Notice that the number 4 has been removed

I would have thought that CAST AS VARCHAR2 would have behaved similarly to CAST AS NUMBER.

select
    cast(1234 as number(3)) as id
from
    dual

Error: ORA-01438: value larger than specified precision allowed for this column

Why does CAST AS VARCHAR2 silently truncate text instead of throwing an error like CAST AS NUMBER does?

Solution

That is a part of ANSI SQL standard and other ANSI SQL compliant RDBMSs do the same:

For fixed length character string targets, if the length of the source equals the fixed length of the target, the result of the CAST is the source string. If the length of the source is shorter than the fixed length of the target, the result of the CAST is the source string padded on the right with however many spaces are required to make the lengths match. If the length of the source is longer than the fixed length of the target, the result of the CAST is a character string that contains as much of the source string as possible – in this case, if the truncated characters are not all spaces, your DBMS will return the SQLSTATE warning 01004 "warning-string data, right truncation".

Unfortunately databases usually can't raise warning and return results together at the same time, so they truncate result silently