SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complex LIKE query

    Hi there,

    Sit down, this is a good 'un.

    I have an interesting problem for searching an invoice number field.

    Due to a total mess of invoice numbers in a system (lots of legacy numbers, some with suffixes, some not) It's virtually impossible for finance staff to get useful search results.

    A fully qualified invoice number should be of the form:

    XXX-XXXXX-XXX [OFFICE-INVOICE-ITEM]

    Each of the office codes is stored separaely to the rest of the number, which leaves me with

    XXXXX-XXX [INVOICE-ITEM]

    However, many legacy invoices do not have the item suffix which denotes what part of the invoice it is (line item, VAT, etc).

    AND

    None of the numbers have any dividing -'s in, meaning I have no way of differentiating between 10099 being 00010-099 or 10099-*** (helpfully they're not zero-filled on the reports I grab the numbers from either).

    The solution I with to implement is three text boxes of the form:

    [***]-[*****]-[***]

    If all boxes are filled completely I will search for an exact match. If the rhs two boxes are filled I will do the same (remember, office is a separate field).

    If the user only puts a number into the middle section, I want to search for all invoices where the last 1/2/3/4/5 digits match and the rest are zero, AND, all invoices where, chopping the last three digits off, the last 1/2/3/4/5 digits match and the rest are zero

    ie, user enters '123' will match '00000123' and '00123099', but not '01234099' and not '00001230'.

    Is it possible to formulate mySQL queries to perform such searches, or will I have to perform simple LIKE searches and parse the resultant array for string matches?

    Many thanks for at least reading.

    Matt.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Is it possible to formulate mySQL queries to perform such searches?

    yes

    does "all boxes are filled completely" mean that you are going to have three search boxes?

    rules like "search for all invoices where the last 1/2/3/4/5 digits match and the rest are zero" will need a bit of clarification, though

    mysql has really powerful substring functions, and you can split your invoice number six ways from sunday, or treat it as a numeric if you wish

    make sure you cfparam the three boxes, and then you can use cfif logic to decide which WHERE clause to generate

    for example, here's the check for all three boxes filled

    <cfif len(box1) and len(box2) and len(box3)>
    where office = #box1#
    and invoice = #box2#
    and item = #box3#
    <cfelseif> ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll do the logic for what the query string is in php.
    If the person entered say '1234' would this be a good query:

    Code:
    SELECT *
    FROM invoice
    WHERE SUBSTRING( invoice_number, 2, 4 ) = "1234"
    OR SUBSTRING( invoice_number, 5, 4 ) = "1234" ;
    Are there better ways of performing such an operation?

  4. #4
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And now I'm aware that it's not a LIKE query at all, can any passing mod rename the thread?


  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i think you're on the right track

    WHERE SUBSTRING( invoice_number, 2, 4 ) = "1234"
    OR SUBSTRING( invoice_number, 5, 4 ) = "1234"

    Code:
    XXX-XXXXX-XXX [OFFICE-INVOICE-ITEM]
        5
    XXXXX-XXX [INVOICE-ITEM]
    1
    XXXXX [INVOICE]
    1
    seems like you want "1234" to match 2,4 and 6,4

    rudy

  6. #6
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I'm right, I've performed a load of test seached and it gets the right data.

    Say I want to search for '1243'

    possible invoice numbers would be

    a) 43256098
    b) 53276554
    c) 01243099
    d) 14124365
    e) 00001243 (looking for malformed invoice number)

    I do want to match c) (2,4) and e) (5,4), but I do not want to match d) (3,4) as this would cross the bounds of invoice number to line item.

    6,4 would take me past the end of the eight characters of the number.
    Last edited by Mincer; Aug 13, 2002 at 16:33.

  7. #7
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, looking at e) again it can only match if all number to the lhs of the query number are zero, the same for c) having to be zero to the left, so the query would be:

    Code:
    SELECT *
    FROM invoice
    WHERE SUBSTRING( invoice_number, 1, 5 ) = "01243"
    OR invoice_number = "00001243" ;

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    6,4 would take me past the end of the eight characters of the number.
    what 8 characters? i counted 13 --

    XXX-XXXXX-XXX

    see, you know more about this app than i do

  9. #9
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by r937
    what 8 characters? i counted 13 --

    XXX-XXXXX-XXX

    see, you know more about this app than i do
    Ah, but I did state this in the first post:

    Originally posted by Mincer
    [***]-[*****]-[***]

    If all boxes are filled completely I will search for an exact match. If the rhs two boxes are filled I will do the same (remember, office is a separate field).


    But thanks for your input, you definately pointed me in the right direction to get my head round the problem.

    Cheers.


Bookmarks

Posting Permissions

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