Re: SQL "like" operator does not work properly on x$ fixed views

Don Burleson wrote:
> Can anyone explain this apparent SQL bug? The "like" filter seems to
> ignore the underscores when querying the x$ fixed tables:
>
> 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

An old chestnut. Have a look at the sql reference manual section under the 'Operators' heading. It explains LIKE fully. The underscore character is a wild card which means 'any single character'. So '%_io_%' in your query means exactly the same as '%io%'.

You need to 'escape' the wild card to use as a literal.. select ksppinm from x$ksppi where ksppinm like '%\_io\_%' ESCAPE '\'; will give you what you want.

Regards
John S Findlay Received on Wed Aug 14 2002 - 07:00:22 CDT