ORA-01425 when using 'like' or 'escape' on NVARCHAR2 column (Doc ID 1470516.1)

Symptoms

You are using using 'like' or 'escape' on NVARCHAR2 column and you get the following error:

ERROR at line 1:
ORA-01425: escape character must be character string of length 1

Here is an example of possible failing syntax:
SQL> select * from t1 where col2 like '%\$$%' escape '\';

Changes

 You may see this problem while setting Cursor_sharing=force.

Cause

The issue is caused by the NVARCHAR2 columns.

The same issue was investigated before in Bug 5726019: ORA-01424 WHEN QUERY USING LIKE WITH ESCAPE OPTION , which was closed as not a bug issue

Since the problematic column is NVARCHAR2, the following syntax should be used:

to_char

or

likec

Solution

Change the query on the NVARCHAR2 column to Use TO_CHAR or LIKEC

Please use any of the following solutions :

select * from t1 where to_char(col2) like '%\?“%' escape '\';

or


select * from t1 where col2 likec '%\?“%' escape '\';
 




an alternative (if you have .net application):
To mitigate this behavior, just use Trim() inside Contains() method against string argument:

https://stackoverflow.com/questions/51129119/ora-01425-escape-character-must-be-character-string-of-length-1