请教一个sql语法问题

Question

Oracle的模糊匹配时使用的通配符：
        _ （下划线）表示任何单个字符
        % （百分号）表示任何零或多个字符
        任何非下划线和百分号的字符都表示本身。逃逸字符: 常用?        也可以使用~
SQL> select * from test;A
----------
thisis%
this?istt
abcdetSQL> select * from test where a like '%?%' escape '?';A
----------
thisis%SQL> select * from test where a like '%~%' escape '~';A
----------
thisis%SQL> select * from test where a like '%??%' escape '?';A
----------
this?istt

Answer 1

呵呵，当被查找的数据中本身就包含了通配符(_,%等），就需要用逃逸字符来区分通配符和实际存在的字符
ex：select * from test where a like 'a#_m_ds' escape '#'
表示查找以a_m开头，以ds结尾的字符
#_表示#后紧接着的_是实际存在的字符
第二个_(_ds)没有逃逸字符（#）在前面作标识，表示通配符其他的boydgmx(授人以鱼不如授人以渔(baidu&google)) 已经说的很清楚了

Answer 2

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