I have a problem in getting rows from the table using the wildcard.

The table: Customer, innodb
The first field: CustomerID, type Varchar.
The second field: CustomerName, type Varchar.

I want to find row with CustomerID = '0001'.

If i use the following:
SELECT * FROM Customer WHERE CustomerID LIKE '0001%'...fail

But if I use:
SELECT * FROM Customer WHERE CustomerID LIKE '000%'....success, but more rows are returned.

However, if i find row with CustomerName = 'MONITA', the result is normal, that is,

SELECT * FROM Customer WHERE CustomerName LIKE 'MONITA%' works....success....only the required row returned.

Is there any problem in using wildcard in Varchar contains only numeric?

Please help!

From the code above the like '000%' will also return the rows that belong to the '0001%' error condition -- since for the % sign either a 0 or 1 would satisfy the current success condition specified. Depending on your record structure for accounting for successes, you may be able to fix this by adding another 0 -> '0000%'.

that's dangerous, because if you ever get a customer with name MONITAS, then you will get more than one row returned

i think you should decide whether you want an exact match (use equality) or all rows that fit the pattern (use LIKE)

In this case, 0001, 0002, 0003....should be returned. Actually, the system should display records with CustomerID from 0001 to 0009. However, the problem is that: when I use LIKE '000%', nothing return at all...just an empty set.

Similiar to the CustomerName search...when I used LIKE 'MONITA%', I expect the system return MONITA, MONITAS, MONITAZ, etc...and it is what I want.

So...do u know why using LIKE 'MONITA%' works, but using LIKE '000%' fails?

By the way, I am using 4.0.15 on Windows 2000.

Thanks

it does? why, what did you change?

because in your first post, you saiddo me a favour, run this query, and post the results here:

select CustomerID from Customer
order by CustomerID limit 10

I am sorry as I did not describe the problem clearly.

I mean...when i use LIKE '000%', the result set includes CustomerID from '0001' to '0009', which is the expected result.

However, when I use LIKE '0001%', I expect only '0001' come out; however, it returns an empty set. It is not expected.

FYI, the query you stated returned '0001' to '0009'.

Please tell me what error message is the system is showing
are passing it as parameter or hard coding

regards
Nanaiah

ramram49, something's wrong with your situation -- maybe CustomerID is not varchar? maybe it's only varchar(4)?

check this out --so it works exactly as expected for me, but not for you?

Hi..the query works well using the SQL statement you provided.

Then I create a new record in the original Customer table, with CustomerID='00011'. When using LIKE '0001%' query, the new record is returned, but '0001' still not showing up. Could it be the problem of the data, as the original data are imported from an Access MDB.

no idea

perhaps it is not '0001' but '00O1' or 'OO01' or ' 0001' or something

you will have to be the detective here, because you have the data

It is 0001 as it returns when i use LIKE '000%'...anyway..thanks for your help..^H^