Summary of Oracle escape characters

Search for fields

Search for fields

Find '%' in the field, which involves the escape characters of Oracle. The following is a summary:

SQL> select * from test;

TEST
--------------------
Sdd_kk
D 'd
Dfsfsa
Dffa % asfs
12345
1% 2345
1% 54321
2% 54321
% 54321
A & B

Nine rows have been selected.

The special characters include %, _, and &. data that may contain these characters may contain errors, or you need to find data that contains these characters.

SQL> select * from test where test like 'sdd _ % 'escape '';

TEST
--------------------
Sdd_kk

Escape Character '';

SQL> select * from test where test like 'sdd \ _ % 'escape '\';

TEST
--------------------
Sdd_kk

Escape Character '\';

SQL> select * from test where test like 'sdd = _ % 'escape' = ';

TEST
--------------------
Sdd_kk

Escape Character: '= ';

SQL> select * from test where test like 'sdd/_ % 'escape '/';

TEST
--------------------
Sdd_kk

Escape Character '/';

SQL> select * from test where test like 'sddd _ % 'escape 'D ';

Unselected row

The Escape Character is d, and the escape function is not implemented;

SQL> select * from test where test like '% \ _ %' escape '\';

TEST
--------------------
Sdd_kk

Search for fields containing all.

Similarly, you can use this method to find all fields containing '%:

SQL> select * from test where test like '% \ %' escape '\';

TEST
--------------------
Dffa % asfs
1% 2345
1% 54321
2% 54321
% 54321

However, '&' cannot be searched by escape characters:

SQL> select * from test where test like '% \ & %' escape '\';
Select * from test where test like '% \ & %' escape '\'
*
Row 3 has an error:
ORA-01424: characters missing or invalid after escape characters

You can escape it in another way:

SQL> select ascii ('&') from dual;

ASCII ('&')
----------
38

SQL> select * from test where test like '%' | chr (38) | '% ';

TEST
--------------------
A & B

Escape:

SQL> select * from test where test like '% ''% ';

TEST
--------------------
D 'd

Insert special characters into data

SQL> insert into test values ('test & test ');
Enter the value of test: test
Original Value 1: insert into test values ('test & test ')
New value 1: insert into test values ('testtesttest')-the data is incorrect even though it is inserted.

One row has been created.

SQL> show define
Define "&" (hex 26)
SQL> set define off
SQL> show define
Define OFF
SQL> insert into test values ('test & test ');

One row has been created.

SQL> show escape
Escape OFF
SQL> set escape on
SQL> show escape
Escape "\" (hex 5c)
SQL> insert into test values ('test \ & test ');

One row has been created.

SQL> select * from test;

TEST
--------------------
Sdd_kk
D 'd
Dfsfsa
Dffa % asfs
12345
1% 2345
1% 54321
2% 54321
% 54321
A & B
Testtest

TEST
--------------------
Test & test
Test & test

13 rows have been selected.

SQL> commit;

Submitted.

SQL> select * from test;

TEST
--------------------
Sdd_kk
D 'd
Dfsfsa
Dffa % asfs
12345
1% 2345
1% 54321
2% 54321
% 54321
A & B
Testtest

TEST
--------------------
Test & test
Test & test

13 rows have been selected.

SQL> insert into test values ('test \ % test ');

One row has been created.

SQL> insert into test values ('test \ _ test ');

One row has been created.

SQL> insert into test values ('test \ 'test );
Insert into test values ('test' test)
*
Row 3 has an error:
ORA-00917: missing comma

SQL> insert into test values ('test ');

One row has been created.

SQL> select * from test;

TEST
--------------------
Sdd_kk
D 'd
Dfsfsa
Dffa % asfs
12345
1% 2345
1% 54321
2% 54321
% 54321
A & B
Testtest

TEST
--------------------
Test & test
Test & test
Test % test
Test_test
Test'test

16 rows have been selected.

,