Re: Like displays rows which doesn't match the condition or am i wrong.

From: Daniel Fink <danielfink.work_at_gmail.com>
Date: Thu, 13 Mar 2014 15:55:04 -0600
Message-ID: <53222938.9050505_at_gmail.com>

In Oracle SQL the _ character has special meaning. In this case, means 'match one character'. % means match one or more characters. In the example you are providing, the LIKE clause is interpreted as Match the pattern where the character string is 'a1' followed by 1 character followed by 0 or more characters.

To match strings with the character '_' in them, you will need to use the escape character '\' (or whatever you have defined it as) in the LIKE string.

For example (off the top of my head...no access to a nonclient db right now)

LIKE 'a1\_%' should return strings matching the literal value of 'a1_' and 0 or more following characters.

Regards,
Daniel Fink

On 3/13/2014 3:46 PM, Veerabasaiah C wrote:
> SQL> select * from test where c1 like 'a1_%';
>
> C1
> ----------
> a1_a2
> a1_a3
> a1_a4
>
> SQL> insert into test values('a1a4');
>
> 1 row created.
>
> SQL> select * from test where c1 like 'a1_%';
>
> C1
> ----------
> a1_a2
> a1_a3
> a1_a4
> a1a4
>
> SQL>
>
> In the above select like displays "a1a4", for the search criteria it
> should display anything with the "a1_" but the last row doesn't even
> have a underscore.
>
>
> --
> Veerabasaiah C B
> "Only put off until tomorrow what you are willing to die having left
> undone. - Picasso"

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 13 2014 - 22:55:04 CET