Re: LIKE and ESCAPE problem

From: damorgan <damorgan_at_exesolutions.com>
Date: Wed, 03 Apr 2002 17:01:52 GMT
Message-ID: <3CAB3586.C9D90210_at_exesolutions.com>

I just ran the following:

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Apr 3 09:00:53 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL> set serveroutput on
SQL> declare
  2 v_dummy VARCHAR2(20);
  3 begin

  4     v_dummy := 'ABC_d';
  5     IF v_dummy LIKE '%/_d' ESCAPE '/' THEN
  6            dbms_output.put_line('Yep');
  7     ELSE
  8        dbms_output.put_line('Nope');
  9     END IF;

 10 end;
 11 /
Yep

PL/SQL procedure successfully completed.

SQL> declare
  2 v_dummy VARCHAR2(20);
  3 begin

  4     v_dummy := 'ABCd';
  5     IF v_dummy LIKE '%/_d' ESCAPE '/' THEN
  6            dbms_output.put_line('Yep');
  7     ELSE
  8        dbms_output.put_line('Nope');
  9     END IF;

 10 end;
 11 /
Nope

PL/SQL procedure successfully completed.

SQL> Works fine for me.

Dan Morgan

Marty Tipipn wrote:

> I must be doing something wrong, but I'll be darned if I can figure it
> out.
>
> What I want to do is test whether a string ends with the literal value
> '_d' - so I'm using the LIKE operator with ESCAPE as follows:
>
> declare
> v_dummy VARCHAR2(20);
> begin
> v_dummy := 'ABC_d';
> IF v_dummy LIKE '%/_d' ESCAPE '/' THEN
> dbms_output.put_line('Yep');
> ELSE
> dbms_output.put_line('Nope');
> END IF;
> end;
>
> Even though v_dummy truly does end with '_d', I always get "Nope" as
> the DBMS output.
>
> Note that if I change the like clause to read
>
> LIKE '%C/_d' ESCAPE '/'
>
> then I get "Yep" as my DBMS output. There's something about escaping
> after a wildcard that's causing a problem.
>
> Running this under Oracle 8i, v8.1.6
>
> Any hints would be appreciated - direct e-mail replies especially.
>
> Thanks
>
> -Marty
> martyt_at_pobox.com
Received on Wed Apr 03 2002 - 19:01:52 CEST