Error running query in oracle with extended ASCII character (128-255)

Question

I have a query that need to run for SQL,MYSQL,ORACLE,Postgres.Basically I have a list of record where I want to get the record which matches the input pattern when searching.

For example

SELECT * FROM BOOKS WHERE NAME LIKE '%TEST¤_1%' ESCAPE '¤'

(This query is just an example of What I am actually trying to run).

I have used the escape '¤' (ascii code - 164) to escape the wildcard (underscore character) so for example If I search the 1,I would replace the _ character to ¤ so It would give me books containing the name 1 only and not the books containing any character before 1.

It is running fine for mysql,postgres, and sql but in oracle when I run the above query It is throwing the below error on latest version of oracle.

ORA-01425: escape character must be character string of length 1
01425. 00000 -  "escape character must be character string of length 1"
*Cause:    Given escape character for LIKE is not a character string of
       length 1.
*Action:   Change it to a character string of length 1.

It is working fine in 11G version of oracle.

Also the NLS_CHARACTERSET for 11G is WE8MSWIN1252 and for latest oracle version is AL32UTF8.

When running below query does give me length 1.

SELECT LENGTH('¤') FROM BOOKS

Do we get the option to enable support for extended ascii code(character code 128-255)) when installing the oracle or Do I need to use any other character to escape the wildcard character(_) If yes what other character Can I use?

Answer 1

Here is one way to do this - using the BEL character, with ASCII value 7.

with
  sample_data (name) as (
    select 'TWITTER'  from dual union all
    select 'MY_PHONE' from dual union all
    select 'MYOHMY'   from dual
  )
select name
from   sample_data
where  name like 'MY' || chr(7) || '_%' escape chr(7)
;

NAME
---------
MY_PHONE

Notice the use of the CHR() function to enter special characters. If you use other mechanisms (keyboard combinations for example, using various special keys) you don't have full control; chr(7) for the BEL character doesn't have that problem.