using escape character whitin oracle

Oracle allows the assignment of special escape characters to tell Oracle that the character is interpreted literally. Certain characters such as the underscore “_” are not interpreted literally because they have special meaning within Oracle.

In the example below, we want to find all Oracle parameter that relate to I/O, so we are tempted to use the filter LIKE “%_io_%’. Below we will select from the x$ksppi fixed table, filtering with the LIKE clause:

SQL> select ksppinm from x$ksppi where ksppinm like '%_io_%';

KSPPINM

----------------------------------------------------------------

sessions

license_max_sessions

license_sessions_warning

_session_idle_bit_latches

_enable_NUMA_optimization

java_soft_sessionspace_limit

java_max_sessionspace_size

_trace_options

_io_slaves_disabled

dbwr_io_slaves

_lgwr_io_slaves

As you can see above, we did not get the answer we expected. The SQL displayed all values that contained “io”, and not just those with an underscore. To remedy this problem, Oracle SQL supports an ESCAPE clause to tell Oracle that the character is to be interpreted literally:

SQL> select ksppinm from x$ksppi where ksppinm like '%_io_%' ESCAPE '';

KSPPINM

----------------------------------------------------------------

_io_slaves_disabled

dbwr_io_slaves

_lgwr_io_slaves

_arch_io_slaves

_backup_disk_io_slaves

backup_tape_io_slaves

_backup_io_pool_size

_db_file_direct_io_count

_log_io_size

fast_start_io_target

_hash_multiblock_io_count

_smm_auto_min_io_size

_smm_auto_max_io_size

_ldr_io_size

[@more@]