News
Abstract
In the tradition of previous Technology Refreshes, a new database SQL built-in function is added to Db2 for i. TRY_CAST provides a unique and powerful approach for using SQL to fully analyze the validity of the data within non-SQL database files.
Content
The TRY_CAST() built-in function is similar to the existing CAST() built-in function.
The important difference between TRY_CAST & CAST is that when TRY_CAST is used and the target data does not adhere to the rules for the intended data type, a NULL value is returned instead of failing the query. By returning a NULL value, it becomes possible and easy to perform a full analysis of non-SQL tables, determining which rows contain invalid or malformed data.
Example:
--
-- Do I have invalid data in my file?
--
select rrn(t) as rrn_with_bad_data, hex(sales) as sales_raw_form
from toystore.sales t
where sales is not null and
TRY_CAST(sales as integer) is null;
See the SQL Reference for details: TRY_CAST
Was this topic helpful?
Document Information
Modified date:
09 May 2022
UID
ibm16575533