LiKE with COLLATION and ESCAPE

View All Public Scripts Login to Run Script

Script Name LiKE with COLLATION and ESCAPE
Visibility Unlisted - anyone with the share link can access
Description with specific collation, LIKE '\_%' ESCAPE '\' gives unexpected results.
Area NLS
Contributor berx
Created Thursday February 25, 2021

Statement 1

alter session set nls_sort=german_ai

Statement processed.

Statement 2

alter session set nls_comp=linguistic

Statement processed.

Statement 3

ALTER SESSION SET DEFAULT_COLLATION=german_ai

Statement processed.

Statement 4
```
drop table t_object
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 5

create table t_object (name varchar2(12))

Table created.

Statement 6

INSERT INTO "T_OBJECT" (NAME) VALUES ('_A')

1 row(s) inserted.

Statement 7

INSERT INTO "T_OBJECT" (NAME) VALUES ('_Ä')

1 row(s) inserted.

Statement 8

INSERT INTO "T_OBJECT" (NAME) VALUES ('A')

1 row(s) inserted.

Statement 9

INSERT INTO "T_OBJECT" (NAME) VALUES ('_B')

1 row(s) inserted.

Statement 10

INSERT INTO "T_OBJECT" (NAME) VALUES ('_')

1 row(s) inserted.

Statement 11

INSERT INTO "T_OBJECT" (NAME) VALUES ('\A')

1 row(s) inserted.

Statement 12

INSERT INTO "T_OBJECT" (NAME) VALUES ('.A')

1 row(s) inserted.

Statement 13
```
commit
```
```
Statement processed.
```

Statement 14

select * from t_object where name like '\_' escape '\'


NAME
_

NAME
_

Statement 15

select * from t_object where name like '_'


NAME
A
_

2 rows selected.

NAME
A
_

Statement 16

select * from t_object where name like '\_%' escape '\'


NAME
\A

NAME
\A

Statement 17

select * from t_object where name like ('\_%' COLLATE GERMAN_AI ) escape '\'


NAME
_A
_Ä
_B
_

4 rows selected.

NAME
_A
_Ä
_B
_

Statement 18

select * from t_object where (name COLLATE GERMAN_AI ) like '\_%' escape '\'


NAME
_A
_Ä
_B
_

4 rows selected.

NAME
_A
_Ä
_B
_

Statement 19

select * from t_object where name like '\_A' escape '\'


NAME
_A
_Ä

2 rows selected.

NAME
_A
_Ä

Statement 20

explain plan for select * from t_object where name like '\_%' escape '\'

Statement processed.

Statement 21

select * from table(dbms_xplan.display)


PLAN_TABLE_OUTPUT
Plan hash value: 3775084319
 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     4 |    32 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_OBJECT |     4 |    32 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("NAME" LIKE '\_%' ESCAPE )

13 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 3775084319

------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 4 \| 32 \| 3 (0)\| 00:00:01 \|
\|* 1 \| TABLE ACCESS FULL\| T_OBJECT \| 4 \| 32 \| 3 (0)\| 00:00:01 \|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NAME" LIKE '\_%' ESCAPE )

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database