Oracle TO_BOOLEAN Function

Oracle AI Database 26ai has a BOOLEAN data type in SQL, but data rarely arrives as BOOLEAN: flags come in as 'Y' and 'N', 'yes' and 'no', 'true' and 'false', or 1 and 0. TO_BOOLEAN converts such strings and numbers into true BOOLEAN values, and with DEFAULT ON CONVERSION ERROR it handles values it does not recognize without failing.

Code for This Guide

The main example is in the examples/conversion-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

to_boolean(expr [default return_value on conversion error])

Convert Strings

Example:

select v, to_boolean(v) as bool
from   (values ('true'), ('yes'), ('on'), ('1'), ('false'), ('no'), ('off'), ('0')) t (v);

Output:

V        BOOL
________ ________
true     true
yes      true
on       true
1        true
false    false
no       false
off      false
0        false

8 rows selected.

Abbreviations, Numbers, and Defaults

The single letters are accepted in either case, any nonzero number is true, and DEFAULT ON CONVERSION ERROR supplies a value for anything unrecognized.

Example:

select to_boolean('T') as t, to_boolean('y') as y, to_boolean(0) as zero,
       to_boolean(5) as five, to_boolean(-1) as minus_one,
       to_boolean('maybe' default false on conversion error) as maybe
from   dual;

Output:

T       Y       ZERO     FIVE    MINUS_ONE    MAYBE
_______ _______ ________ _______ ____________ ________
true    true    false    true    true         false

Without the default, an unrecognized string stops the query:

Example:

select to_boolean('maybe') as maybe from dual;

Output:

Error starting at line : 1
In command -
select to_boolean('maybe') as maybe from dual
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-61800: invalid boolean literal: maybe

Things to Know

• Use TO_BOOLEAN when migrating CHAR(1) 'Y'/'N' flags to BOOLEAN columns: update t set active = to_boolean(active_flag).
• Leading and trailing spaces in strings are worth trimming first if your data has them.
• To go the other way, TO_CHAR of a BOOLEAN returns 'TRUE' or 'FALSE', and CASE can produce any text you prefer.

Related Guides

• How NULL Works in Oracle Conditions

Conclusion

TO_BOOLEAN turns the usual spellings of yes and no, and numbers, into the BOOLEAN data type of Oracle AI Database 26ai. Nonzero numbers are true, unrecognized strings raise ORA-61800, and DEFAULT ON CONVERSION ERROR lets you choose a value for them instead.