Hi,

I have a table with a VARCHAR2 field (Oracle 9i), and I'm trying to do a
SELECT using a "where ... like ...", and am having a problem.


The field (myfield) in the records in the table contain a bunch of text,
and in the middle, there's something like:

"....SerialNumber: [0a]..."

"....SerialNumber: [0b]..."

etc.


My SELECT looks something like:

SELECT * from mytablename where lower(myfield) like
lower('%serialnumber: [ 0a]%')

is returning no rows.

If I change the SELECT to:

SELECT * from mytablename where lower(myfield) like
lower('%serialnumber:%')

the query does return rows (too many of them!!).

Can someone tell me what is wrong with this?

Thanks,
Jim

On Sat, 24 Jul 2004 13:42:50 -0400, ohaya <ohaya@cox.net> wrote:

>there's something like:
>
>"....SerialNumber: [0a]..."

No space after the [.
>
>My SELECT looks something like:
>
>SELECT * from mytablename where lower(myfield) like
>lower('%serialnumber: [ 0a]%')
^

A space after the [. They don't match.

--
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

Andy Hassall wrote:
>
> On Sat, 24 Jul 2004 13:42:50 -0400, ohaya <ohaya@cox.net> wrote:
>
> >there's something like:
> >
> >"....SerialNumber: [0a]..."
>
> No space after the [.
> >
> >My SELECT looks something like:
> >
> >SELECT * from mytablename where lower(myfield) like
> >lower('%serialnumber: [ 0a]%')
> ^
>
> A space after the [. They don't match.
>


Hi Andy,

I was trying to be careful when I typed my original post, but may've
messed up, but the fields in the records have a space/blank after the
colon and after the left brackets ("[ 0a]"), and in the WHERE in the
SELECT that I'm using, I'm putting spaces/blanks in both those places,
and am not getting any rows back.

What's puzzling is if I put a string up to and including the left
bracket in the WHERE, the query returns information. It's only if I try
to include the text/data past the "[" that the query fails.

Is it possible the lower() is messing up the "0a", or something like
that? I've tried using upper() instead, but same problem.

Jim

> Hi Andy,
>
> I was trying to be careful when I typed my original post, but may've
> messed up, but the fields in the records have a space/blank after the
> colon and after the left brackets ("[ 0a]"), and in the WHERE in the
> SELECT that I'm using, I'm putting spaces/blanks in both those places,
> and am not getting any rows back.
>
> What's puzzling is if I put a string up to and including the left
> bracket in the WHERE, the query returns information. It's only if I try
> to include the text/data past the "[" that the query fails.
>
> Is it possible the lower() is messing up the "0a", or something like
> that? I've tried using upper() instead, but same problem.
>
> Jim


Hi,

Ok, I've figured it out. The field actually has the string something
like:

....SerialNumber: [ 0a]...

i.e., the info between the brackets is 6-characters, left-blank-filled.
I was looking at the info in View/Edit Contents in OEM, and you can't
see those 4 blanks, but when I did a query using SQLPlus, they showed up
...

Thanks,
Jim