12 Replies Latest reply: Jul 11, 2006 3:24 AM by 522534 RSS

    BUG with ESCAPE CHARACTER in SQL QUERY ???

    486717
      Hi !
      I'm trying to escape some special characters (%, ', _ ) with Oracle 9.2 . I read in oracle documentation and on forums how to make this. The way given is :

      SELECT last_name
      FROM employees
      WHERE last_name LIKE ’%\%%\_%’ ESCAPE ’\’;

      BUT this doesn't work for me:
      "ORA-01425: Given escape character for LIKE is not a character string of length 1"

      I try others ways :
      - I changed the character '\' by '*' or '+', '!', ... and finally with 'a', 'c' but the result is the same.
      - When i changed '\' by '\\' or 'N~', '\\\\' , ... the query is well-executed, but no row is found ,even if in my table there's employees with last_name 'aaa_zzz' .

      If anybody can explained me why this? Or better if anybody knows how to solve this? Thanks a lot because i have no more ideas now ( is it an SQL bug, or a problem of configuration).
        • 1. Re: BUG with ESCAPE CHARACTER in SQL QUERY ???
          Laurent Schneider
          probably you are not doing this in sqlplus but in a shell that also interpret \
          • 2. Re: BUG with ESCAPE CHARACTER in SQL QUERY ???
            Warren Tolentino
            SELECT last_name
            FROM employees
            WHERE last_name LIKE ’%\%%\_%’ ESCAPE ’\’;
            it seems that the single quote character that you are using is like this:

            i'm not sure how you got it but single quote character recognized by oracle is: '
            • 3. Re: BUG with ESCAPE CHARACTER in SQL QUERY ???
              486717
              unfortunately this is in sqlplus and not in a shell... it's too weird !
              May be it's something about the character set .
              Is there any option or command to print the character set used and a command
              to update it with the default value.
              Thanks a lot at all for your help.
              • 4. Re: BUG with ESCAPE CHARACTER in SQL QUERY ???
                Laurent Schneider
                strange. version? os?
                SQL> SELECT ename
                  2  FROM emp
                  3 WHERE ename LIKE '%\%%\_%' ESCAPE '\';
                
                no rows selected
                
                SQL> SELECT ename
                  2  FROM emp
                  3  WHERE ename LIKE '%\%%\_%' ESCAPE '\''';
                WHERE ename LIKE '%\%%\_%' ESCAPE '\'''
                                                  *
                ERROR at line 3:
                ORA-01425: escape character must be character string of length 1
                try to output the exact error message and query
                • 5. Re: BUG with ESCAPE CHARACTER in SQL QUERY ???
                  454375
                  hi
                  I could not understand ur actual requirement but u can try with below query
                  to get 'aaa_zzz'
                  SELECT last_name
                  FROM employees
                  WHERE last_name LIKE '%\_%' ESCAPE '\';

                  Thanks
                  Satya
                  • 6. Re: BUG with ESCAPE CHARACTER in SQL QUERY ???
                    52663
                    We ran into this error message on Friday. We have found a resolution for the problem.

                    I'll share the exact table structure from our application with you so that you can follow along with my explanation of the problem.

                    create table KIT_COMPONENT
                    (
                    CMPNT_ID NUMBER(10) not null,
                    SBS_NO NUMBER(5) not null,
                    CMPNT_NAME NVARCHAR2(30) not null
                    )
                    ;

                    Notice that CMPNT_NAME is defined as an NVARCHAR2.

                    INSERT INTO kit_component VALUES (1,1, 'C%_1');
                    INSERT INTO kit_component VALUES (2,1, 'C_%2');
                    INSERT INTO kit_component VALUES (3,1, 'CMP3');

                    This query causes the ORA-1425 error:
                    SELECT *
                    FROM kit_component t
                    WHERE upper(cmpnt_name) LIKE '%\_%' ESCAPE '\'
                    ;

                    This query does not cause the ORA-1425 error:
                    SELECT *
                    FROM kit_component t
                    WHERE upper(cmpnt_name) LIKE TRANSLATE('%\_%' USING NCHAR_CS) ESCAPE TRANSLATE('\' USING NCHAR_CS)
                    ;

                    The problem appears to be due to Oracle’s inability to implicitly convert data on the right-hand side of the predicate into the national character set. You are specifying ‘\’ as your escape character in the database character set. CMPNT_NAME is in the national character set. Oracle seems to be able to convert the LIKE condition (‘%\_%’) implicitly but you can explicitly convert that for Oracle as well. For your query to work you need to convert the escape character (at least) into the national character set so that Oracle understands what you want to do.

                    The query will also work if you convert the left-hand side of the predicate into the database character set.

                    Thanks,
                    Adam
                    • 7. Re: BUG with ESCAPE CHARACTER in SQL QUERY ???
                      Nicolas.Gasparotto
                      This query causes the ORA-1425 error:
                      SELECT *
                      FROM kit_component t
                      WHERE upper(cmpnt_name) LIKE '%\_%' ESCAPE '\'
                      ;
                      And for me, it works well...
                      SQL> create table KIT_COMPONENT
                        2  (
                        3  CMPNT_ID NUMBER(10) not null,
                        4  SBS_NO NUMBER(5) not null,
                        5  CMPNT_NAME NVARCHAR2(30) not null
                        6  )
                        7  ;

                      Table created.

                      SQL> INSERT INTO kit_component VALUES (1,1, 'C%_1');

                      1 row created.

                      SQL> INSERT INTO kit_component VALUES (2,1, 'C_%2');

                      1 row created.

                      SQL> INSERT INTO kit_component VALUES (3,1, 'CMP3');

                      1 row created.

                      SQL> SELECT *
                        2  FROM kit_component t
                        3  WHERE upper(cmpnt_name) LIKE '%\_%' ESCAPE '\'
                        4  ;

                        CMPNT_ID     SBS_NO CMPNT_NAME
                      ---------- ---------- ------------------------------
                               1          1 C%_1
                               2          1 C_%2

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

                      BANNER
                      --------------------------------------------------------------
                      Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
                      Nicolas.
                      • 8. Re: BUG with ESCAPE CHARACTER in SQL QUERY ???
                        27876
                        and, does not seem to work on 9.2.0.5. it seems that one of the patchset, 9.2.0.6 or 9.2.0.7 seems to have fixed this issue.
                        SQL> SELECT *
                          2  FROM kit_component t
                          3  WHERE upper(cmpnt_name) LIKE '%\_%' ESCAPE '\'
                          4  ;
                        WHERE upper(cmpnt_name) LIKE '%\_%' ESCAPE '\'
                                                                   *
                        ERROR at line 3:
                        ORA-01425: escape character must be character string of length 1


                        SQL> disconnect
                        Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
                        With the Partitioning, OLAP and Oracle Data Mining options
                        JServer Release 9.2.0.5.0 - Production
                        SQL>
                        • 9. Re: BUG with ESCAPE CHARACTER in SQL QUERY ???
                          Nicolas.Gasparotto
                          Interesting point Kamal.
                          Tomorrow, I will test on a 9.2.0.4 database (today, I haven't this last one on my PC), just to see.

                          Nicolas.
                          • 10. Re: BUG with ESCAPE CHARACTER in SQL QUERY ???
                            Nicolas.Gasparotto
                            Kamal,

                            That's ok, same error on a 9.2.0.4 db. So it's not a bug from 9.2.0.5 but before, and as you said, migrate to 9.2.0.6 or 9.2.0.7 seems solve this case.

                            Nicolas.
                            • 11. Re: BUG with ESCAPE CHARACTER in SQL QUERY ???
                              520649
                              set escape off it should work
                              • 12. Re: BUG with ESCAPE CHARACTER in SQL QUERY ???
                                522534
                                Hi!

                                Is last_name of type nvarchar? Try TO_CHAR function - it will help:

                                SELECT last_name
                                FROM employees
                                WHERE TO_CHAR( last_name ) LIKE ’%\%%\_%’ ESCAPE ’\’;

                                Greetings,
                                Maciek