SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast teezecrost's Avatar
    Join Date
    Oct 2003
    Location
    Winnipeg, MB, Canada
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    comparing without whitespace & caps

    Hey, everyone.
    I need to write some code that checks to see if a user-submitted street address already exists in a SQL Server database. I'd like to minimize the records missed by comparing the addresses as strings with no whitespace, and converted to all lower-case. If possible, I'd also like to remove any special characters.
    Any idea how I'd do this?
    Thanks a bunch in advace!

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Exactly how depends on what platform you are working in to communicate with the database, but it would go something like this:

    1) convert string to lower case.
    2) use a regular expression to replace whitespace with blank characters
    3) hash this value and use that to compare to similarly created hashes you store in each record.

  3. #3
    SitePoint Enthusiast teezecrost's Avatar
    Join Date
    Oct 2003
    Location
    Winnipeg, MB, Canada
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess the thing I'm not sure about is what functions to use in the actual sql query.. I'm using coldfusion, but what functions does SQL have to remove whitespace etc in the WHERE clause? IE - WHERE nowhitespacefunction(address)='#stringwithwhitespaceremoved#'

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the part i don't understand is why you don't just do this in coldfusion

    cleanse the address on the way into the database, then just compare cleansed strings

    in fact if you give the cleansed address a unique constraint, you won't even need to do the select to see if you already have that value, the database does this for you automatically
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast teezecrost's Avatar
    Join Date
    Oct 2003
    Location
    Winnipeg, MB, Canada
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I want the user to see his/her info formatted the same way they typed it when they look up their info. The string I'm using to compare (the user-input), I'm formatting in coldfusion, but the columns i'm comparing TO in the database have to be stored in a visually appealing way that is true to the way the user entered the info in the first place.

  6. #6
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    You need to store two columns--the actual data (probably should be several if it is an address) and the "hash" for comparison purposes. This way you can do the lookup based on the hash using a SELECT statement rather than selecting everything, running some string manipulation and comparing it to whatever you are comparing it to.

  7. #7
    SitePoint Enthusiast teezecrost's Avatar
    Join Date
    Oct 2003
    Location
    Winnipeg, MB, Canada
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Many Thanks

    Alright, if that's what it comes down to, then that's that.
    Thanks for the input, everyone!


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
  •