Re: '%' in variable used in like clause (PL/SQL)

From: Jurij Modic <jmodic_at_src.si>
Date: Thu, 22 Apr 1999 17:34:16 GMT
Message-ID: <371f5d5b.5024276@news.arnes.si>

On Wed, 21 Apr 1999 23:24:22 GMT, Remi Delon <rdelon_at_my-dejanews.com> wrote:

>Hi, I'm trying to use variables to build a WHERE clause with a LIKE operator.
>
>My problem is that puting the '%' sign in the variable doesn't seem to be
>working. For example, if I try something like :
>declare
> val char(10);
> res integer;
>begin
> val:='%';
> select count(*) into res from my_table where col like val;
>end;
>
>No rows are selected, even though my_table contains lots of datas !
>I've noticed that if, let's say, 'col' is a 4-digit integer and I use
>val:='____' instead of val:='%', it's working and all rows are selected !!
>
>Any ideas on what's going on ?

Declare your variable VAL as VARCHAR2 instead of CHAR and your select will return expected result. If declared as CHAR(10), VAL:='%' actualy results as '% '.

>Thanks
>
>Remi.

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Apr 22 1999 - 12:34:16 CDT