SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    1,967
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What would be the best where clause?

    I have a control panel where the site owner can search for products by product number. It happens quite often though that they make a typo: BAR 2131G instead of BAR2131G (notice the space in the first one. Or instead of the G at the end they type a H (next to the G on the keyboard). I would like to return some alternative records in case something like that occur. What would be the best where clause in such a case? A wildcard LIKE % % or are there any other suggestions?

    Thank you in advance
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  2. #2
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    1,967
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried to return results using these wildcards in my query but it doesn't return any results:
    Code MySQL:
    WHERE
      ( 
        P.ref_number LIKE '%#Url.ref_number#%'
    OR
        P.ref_number LIKE  '%#Url.ref_number#'
    OR
        P.ref_number LIKE  '#Url.ref_number#%'  
      )
    When I type the correct ref_number (EP 272 in this example) the address bar showes ref_number=EP+272 How should I adjust the query so that it will return results when it was entered as EP272 or EP 273
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    how is the value EP273 stored in the table? with a space or without?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    1,967
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    how is the value EP273 stored in the table? with a space or without?
    It is stored as EP 273, with the space!
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    in that case, your query will find it

    by the way, your three conditions with the ORs are redundant

    '%AAA%' will cover both '%AAA' and 'AAA%'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    1,967
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    in that case, your query will find it

    by the way, your three conditions with the ORs are redundant

    '%AAA%' will cover both '%AAA' and 'AAA%'
    But why is it that it doesn't give me a result. I added the '%AAA' and 'AAA%' because I didn't get any results using the '%AAA%' When I use:
    Code MySQL:
    WHERE 
      ref_number = 'Url.ref_number'
    and I use the right ref_number it obviously returns the record I was looking for, but as soon as I replace it in my query with:
    Code MySQL:
    WHERE 
      ref_number LIKE '%Url.ref_number%'
    it doesn't. Can it be, bcause I use two WHERE clauses in the query like this:
    Code CFM:
    <cfif structKeyExists( Url, "ref_number" )>    
      WHERE
          ref_number = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( Url.ref_number )#" />
    <cfelseif structKeyExists( Url, "product" )>
      WHERE
         product_id = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( Url.product )#" />        
    </cfif>
    Excuuus me for using CF code in this post but I didn't know how else to express it
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yeah, you definitely cannot have two WHERE keywords

    why don't you try this ...
    Code:
      WHERE REPLACE(ref_number,' ','') LIKE REPLACE('%#url.ref_number#%',' ','')
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    1,967
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yeah, you definitely cannot have two WHERE keywords

    why don't you try this ...
    Code:
      WHERE REPLACE(ref_number,' ','') LIKE REPLACE('%#url.ref_number#%',' ','')
    That seems to work great! Thank you for that. What is this doing Rudy?

    Need to edit: This works fine for spaces, but when a typo is made, for example EO instead of EP it generates an error.

    Second Edit When I use it hard coded:
    Code MySQL:
    WHERE 
        ref_number LIKE '%EP%'
    It returns the right record!
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  9. #9
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    1,967
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This problem has obviously to do with the space used in ref_number. As example I still used the ref_number as stated before (EP 272) This are the tests so far and the results:

    E > Returns result(s)
    EP > Returns result(s)
    P > Returns result(s)
    EP 2 > Returns result(s)
    P 2 > Returns result(s)
    2 > Returns result(s)
    27 > Returns result(s)
    72 > Returns result(s)
    272 > Returns result(s)

    As soon as I enter anything without the space between the P and 2 in my search no result is returned. I start to wonder if I should use a different data type to store this value, whatever that might be?
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  10. #10
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Search terms of less than four characters are ignored.

  11. #11
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    1,967
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    Search terms of less than four characters are ignored.
    Hi guelphdad. Sorry for my ignorance, but how should I read this information? If you see my list above there are many occasions where results are returned with less than four characters?
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by guelphdad View Post
    Search terms of less than four characters are ignored.
    only for fulltext matching

    he's using LIKE

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Probably not a solution best implemented at the DB level. Do you expect your Ref # in a specific format? Maybe you can pre-process the entered value at the Business Logic Layer prior to hitting the DB.

    Also, I suggest:

    First, trim out spaces and perform other pre-formatting.
    Do a count on exact match.
    If you get one, return that.
    If you get none, do a like-match.
    If no results, cut the search term in half, left and right, and search each, unioning the results.

    Cheers!

  14. #14
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    1,967
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by transio View Post
    Probably not a solution best implemented at the DB level. Do you expect your Ref # in a specific format? Maybe you can pre-process the entered value at the Business Logic Layer prior to hitting the DB.

    Also, I suggest:

    First, trim out spaces and perform other pre-formatting.
    Do a count on exact match.
    If you get one, return that.
    If you get none, do a like-match.
    If no results, cut the search term in half, left and right, and search each, unioning the results.

    Cheers!
    I usually use a pre processed format, but in this case the numbers were already in place and the insisted of keeping them that way. I tried the left indeed with result, but what is bothering me most is the fact that the ref_number without the space isn't returning any results
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  15. #15
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry donboe, i had just finished reading a different thread on fulltext matching and then for some reason thought you were using it too. <hangs head>


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
  •