1

Topic: SQL and comparison strings do not ignore case

All greetings;
Explain to me why at comparing one request considers the register of lines, and another is not present? Why the example 1 considers . Without UPPER, and in the Example 3 it is necessary to use UPPER? Strange that the Example 4  to the Example 1, but without UPPER (owner) does not see lines
The example 1, component_id = ' rdbms ' approaches to a condition of 9055 lines:

SELECT COUNT (*)
FROM v$diag_alert_ext
WHERE component_id = ' rdbms'
AND message_text LIKE ' %R % ';
/*
COUNT (*)
----------
9055
*/

The example 2, in the same DB component_id = ' rdbms ' approaches to a condition of 0 lines:

SELECT COUNT (*)
FROM v$diag_alert_ext
WHERE component_id = ' rdbms';
/*
COUNT (*)
----------
0
*/

The example 3, to condition UPPER (component_id) = UPPER (' rdbms') approaches 9055

SELECT COUNT (*)
FROM v$diag_alert_ext
WHERE UPPER (component_id) = UPPER (' rdbms');
/*
COUNT (*)
----------
11397
*/

Example 4

SELECT COUNT (*)
FROM all_objects a
WHERE a.owner = ' sys'
AND a.object_name LIKE ' %R % ';
/*
COUNT (*)
----------
0
*/

2

Re: SQL and comparison strings do not ignore case

Jafa;
v$diag_alert_ext -  in itself (there curve x $-function) I frequently simply materialize all from it and only then I filter

3

Re: SQL and comparison strings do not ignore case

I do not see yours v $...
And in ALL_OBJECTS

a.owner = ' SYS'

There is a result.

a.owner = ' sys'

There is no result (0).

4

Re: SQL and comparison strings do not ignore case

xtender wrote:

Jafa;
v$diag_alert_ext -  in itself (there curve x $-function) I frequently simply materialize all from it and only then I filter

I already too thought of it, probably this  not official (not ).
Or as here it is told - "The v$diag_alert_ext table is under-documented in Oracle and there are no notes about v$diag_alert_ext on MOSC."

5

Re: SQL and comparison strings do not ignore case

dmdmdm;
v$diag_alert_ext Lies in SYS
For the Example 4 can especially not spend attention, it as 1 and shows analog of the Example that there is a difference ' SYS ' and ' sys'

6

Re: SQL and comparison strings do not ignore case

Jafa;
I a little bit was too lazy to describe its jamb simply: this  works normally if there are no predicates but when you add any predicate which uses fixed table fixed index with even predicates all breaks

7

Re: SQL and comparison strings do not ignore case

Jafa;
https://docs.oracle.com/database/122/RE … RT_EXT.htm