Oracle Escape Characters

引用

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:

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:

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

引用

Oracle databases reserve some special characters with specific meaning and purpose within Oracle environment. These reserved characters include _ (underscore) wild card character which used to match exactly one character, % (percentage) which used to match zero or more occurrences of any characters and ‘ (apostrophe or quotation mark) which used to mark the value supplied. These special characters will not be interpreted literally when building SQL query in Oracle, and may caused error in results returned especially when performing string search with LIKE keyword. To use these characters so that Oracle can interpret them literally as a part of string value instead of preset mean, escape character has to be assigned.

Oracle allows the assignment of special escape characters to the reserved characters in Oracle can be escaped to normal characters that is interpreted literally, by using ESCAPE keyword.

For example, to select the name of guests with _ (underscore) in it, use the following statement:

SELECT guest_name FROM guest_table WHERE name LIKE ‘%\_%’ ESCAPE ‘\’;

Without specifying the \ (backslash) as escape clause, the query will return all guest names, making the unwanted results problem.

The above syntax will not work on ‘ (quote). To escape this quotation mark and to display the quote literally in string, insert another quote (total 2 quotes) for every quote that want to be displayed. For example:

SELECT ‘This will display line with quote’’s word.’ FROM temp_table;
SELECT ‘This will display ””double quoted”” word.’ FROM temp_table;

will return the following respectively:

This will display line with quote’s word.
This will display ”double quoted” word.