Why does CAST truncate text instead of throwing an error?

Question

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?

score 5 · Accepted Answer · answered Sep 20 '20 at 16:07

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

May I request you to provide the link to the document ? But why does it behave differently in case of number then.. Thank you. — Sujitmohanty30, Sep 20 '20 at 16:19
Unfortunately that document is not free and we can't redistribute it. But you can buy it on ansi.org In case of number types it should return `sqlstate error`, not warnings — Sayan Malakshinov, Sep 20 '20 at 17:02
Btw you can try to get it here: https://modern-sql.com/standard — Sayan Malakshinov, Sep 20 '20 at 17:03

Why does CAST truncate text instead of throwing an error?

1 Answers1