IBM Support




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.


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.



-- 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

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CHeAAM","label":"IBM i Db2"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.4.0;and future releases"}]

Document Information

Modified date:
09 May 2022