Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Thanks for the question, Ken.

Asked: January 09, 2018 - 6:05 pm UTC

Last updated: January 10, 2018 - 4:47 am UTC

Version: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

If I use the tilde to escape an underscore in SQL it works fine:

select x from
(
select 'A_Babc' x from dual
union all
select 'A$Bxyz' x from dual
)
where x like 'A~_B%' escape '~'

X
------
A_Babc

However, when using the backslash to escape it, I get an error:
select x from
(
select 'A_Babc' x from dual
union all
select 'A$Bxyz' x from dual
)
where x like 'A\_B%' escape '\'

Error report -
SQL Error: 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.

This makes me suspect that this feature is not reliable - period!

I welcome your feedback,
Ken

and Connor said...

I can't reproduce that on any database I have

SQL> select banner from v$version where rownum = 1;

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

1 row selected.

SQL>
SQL> select x from
  2  (
  3  select 'A_Babc' x from dual
  4  union all
  5  select 'A$Bxyz' x from dual
  6  )
  7  where x like 'A~_B%' escape '~';

X
------
A_Babc

1 row selected.

SQL>
SQL>
SQL> select x from
  2  (
  3  select 'A_Babc' x from dual
  4  union all
  5  select 'A$Bxyz' x from dual
  6  )
  7  where x like 'A\_B%' escape '\';

X
------
A_Babc

1 row selected.

SQL> select banner from v$version where rownum = 1;

BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL>
SQL> select x from
  2  (
  3  select 'A_Babc' x from dual
  4  union all
  5  select 'A$Bxyz' x from dual
  6  )
  7  where x like 'A~_B%' escape '~';

X
------
A_Babc

SQL>
SQL>
SQL> select x from
  2  (
  3  select 'A_Babc' x from dual
  4  union all
  5  select 'A$Bxyz' x from dual
  6  )
  7  where x like 'A\_B%' escape '\';

X
------
A_Babc

SQL> select banner from v$version where rownum = 1;

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL>
SQL> select x from
  2  (
  3  select 'A_Babc' x from dual
  4  union all
  5  select 'A$Bxyz' x from dual
  6  )
  7  where x like 'A~_B%' escape '~';

X
------
A_Babc

SQL>
SQL>
SQL> select x from
  2  (
  3  select 'A_Babc' x from dual
  4  union all
  5  select 'A$Bxyz' x from dual
  6  )
  7  where x like 'A\_B%' escape '\';

X
------
A_Babc

Are you sure that you don't have an errant space in there anywhere ? eg

SQL> select x from
  2  (
  3  select 'A_Babc' x from dual
  4  union all
  5  select 'A$Bxyz' x from dual
  6  )
  7  where x like 'A\_B%' escape '\ ';   -- trailing space
where x like 'A\_B%' escape '\ '
                            *
ERROR at line 7:
ORA-01425: escape character must be character string of length 1

or running it in some environment where the backslash is special to the environment itself (eg unix shell)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

SQL

Using escape character --> ORA-01425

Breadcrumb

Question and Answer

You Asked

and Connor said...

More to Explore

SQL