Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2002
    Posts
    16

    Unanswered: WHERE .... LIKE ... problem

    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!

  2. #2
    Join Date
    Jan 2004
    Location
    Kennesaw, GA
    Posts
    10
    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%'.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Re: WHERE .... LIKE ... problem

    Originally posted by ramram49
    SELECT * FROM Customer WHERE CustomerName LIKE 'MONITA%' works....success....only the required row returned.
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2002
    Posts
    16
    Originally posted by bzakrzew
    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%'.
    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
    Last edited by ramram49; 01-05-04 at 21:11.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do u know why using LIKE 'MONITA%' works, but using LIKE '000%' fails?
    it does? why, what did you change?

    because in your first post, you said
    But if I use:
    SELECT * FROM Customer WHERE CustomerID LIKE '000%'....success, but more rows are returned.
    do me a favour, run this query, and post the results here:

    select CustomerID from Customer
    order by CustomerID limit 10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2002
    Posts
    16
    Originally posted by r937
    it does? why, what did you change?

    because in your first post, you said

    do 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'.

  7. #7
    Join Date
    Dec 2003
    Posts
    13

    Re: WHERE .... LIKE ... problem

    Originally posted by ramram49
    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!

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

    regards
    Nanaiah

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ramram49, something's wrong with your situation -- maybe CustomerID is not varchar? maybe it's only varchar(4)?

    check this out --
    Code:
    create table testLIKE
    ( CustomerID varchar(5)
    , Product varchar(12)
    );
    insert into testLIKE values 
     ('0001','widget') 
    ,('0002','doodad ') 
    ,('0003','thingie') 
    ,('0004','gizmo') 
    ,('0005','gadget') 
    ,('0006','dingus') 
    ,('0007','gewgaw') 
    ,('0008','knickknack') 
    ,('0009','whatnot') 
    ,('00010','bric-a-brac') 
    ,('00011','folderol')
    
    select * from testLIKE
    where CustomerID LIKE '0001%'
    
    CustomerID  Product  
    0001 widget 
    0002 doodad 
    0003 thingie 
    0004 gizmo 
    0005 gadget 
    0006 dingus 
    0007 gewgaw 
    0008 knickknack 
    0009 whatnot 
    00010 bric-a-brac 
    00011 folderol 
    
    select * from testLIKE
    where CustomerID LIKE '0001%'
    
    CustomerID  Product  
    0001  widget 
    00010 bric-a-brac 
    00011 folderol
    so it works exactly as expected for me, but not for you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2002
    Posts
    16
    Originally posted by r937
    ramram49, something's wrong with your situation -- maybe CustomerID is not varchar? maybe it's only varchar(4)?

    check this out --
    Code:
    create table testLIKE
    ( CustomerID varchar(5)
    , Product varchar(12)
    );
    insert into testLIKE values 
     ('0001','widget') 
    ,('0002','doodad ') 
    ,('0003','thingie') 
    ,('0004','gizmo') 
    ,('0005','gadget') 
    ,('0006','dingus') 
    ,('0007','gewgaw') 
    ,('0008','knickknack') 
    ,('0009','whatnot') 
    ,('00010','bric-a-brac') 
    ,('00011','folderol')
    
    select * from testLIKE
    where CustomerID LIKE '0001%'
    
    CustomerID  Product  
    0001 widget 
    0002 doodad 
    0003 thingie 
    0004 gizmo 
    0005 gadget 
    0006 dingus 
    0007 gewgaw 
    0008 knickknack 
    0009 whatnot 
    00010 bric-a-brac 
    00011 folderol 
    
    select * from testLIKE
    where CustomerID LIKE '0001%'
    
    CustomerID  Product  
    0001  widget 
    00010 bric-a-brac 
    00011 folderol
    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  11. #11
    Join Date
    Aug 2002
    Posts
    16
    Originally posted by r937
    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^

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •