SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Comparing strings with spaces and different cases

    I would like to run a query on my database to check if a postcode exists in the database.

    The postcode is generally of the format

    BA21 9XY

    However it may be written as:

    ba21 9xy (LOWERCASE)
    BA219XY (NO SPACES)

    I have currently got the following that deals with the case difference but can't figure out how to deal with spaces. Trim/RTrim/LTrim appears to only remove trailing and leading spaces.

    Code:
    $query="SELECT postcode FROM newBusiness WHERE UPPER(postcode) = UPPER('$postcode')";
    Does the following look like a viable solution?

    Code:
    $query="SELECT postcode FROM newBusiness WHERE UPPER(REPLACE(postcode, ' ', '')) = UPPER(REPLACE('$postcode',' ',''))";
    Thanks in advance

    Paul
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by chuckylefrek View Post
    Does the following look like a viable solution?
    let me answer this by asking what happened when you tested it?

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

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, should have phrased my post differently.

    The code I listed did appear to work however I am wondering if it is the most efficient code to use. I have read about using REGEX and LIKE statements and therefore was thinking the code I have tried is likely not to be the most efficent query
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    unfortunately your code is already the most efficient it's gonna get, which isn't very

    to make it more efficient, you have to make the query sargable, which simply means you need to write the query in such a way as to avoid using any functions on the column value

    so instead of

    ... WHERE UPPER(REPLACE(postcode, ' ', '')) = 'something'

    you want to write

    ... WHERE postcode = 'something'

    and then an index on the postcode column can be utilized to optimize the query

    the only way this is going to happen is if you store the data with the UPPER and REPLACE functions already having been applied

    this confirms one of the maxims of data design: perform cleansing functions on data on the way in (INSERTs) instead of on the way out (SELECTs)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy - very helpful reply.

    I did originally just use "WHERE postcode ='$postcode' then realised that some people had entered their postcodes with no spaces and also some in lowercase. I will do as you suggest and ensure all postcodes entered in the database are set to uppercase and look into doing some sort of client side validation to check for lack of spaces in the postcode.

    Thanks again for your very helpful advice.
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  6. #6
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,810
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Perhaps a better solution would be to amend the processing that writes the data into the database to convert it to a standard format at that point. Then you just need to run a one time update of the database to convert the data in the column to that format. Once that is done you'd know that they are all in that format when doing the comparisons and hence the comparisons will be easier to write and faster to run.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


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
  •