SQL Search - agnostic of spaces and dashes?

Discussion in 'PC Hardware, Software and Displays' started by zsouthboy, Dec 7, 2005.

  1. zsouthboy

    Regular

    Joined:
    Aug 1, 2003
    Messages:
    563
    Likes Received:
    9
    Location:
    Derry, NH
    Help from any DB admins -

    How do you deal with user input?

    A little background...

    I have a DB of about 1500 rows, and each one has a unique item number that includes numbers, letters, and dashes (no actual spaces in the item number)

    examples: 55-ADPR-1024, 07-EACH-2213

    The problem I have is that users, like all of us, are lazy, and want to be able to type partial item numbers. Usually they substitute a space for a dash, as its easier to reach, or just don't include it at all.


    I want this to happen

    Search for "RBP4" -> return results for "RBP-4", "RBP", and "RBP4" in the item number field

    What do you guys recommend? I'm thinking something along the lines of split the entered term everywhere a number is next to a letter, and search for the original term, plus the terms separately, plus the terms with a dash between them.
     
  2. Frank

    Frank Certified not a majority
    Veteran

    Joined:
    Sep 21, 2003
    Messages:
    3,187
    Likes Received:
    59
    Location:
    Sittard, the Netherlands
    As long as all your keys have the same format, that's what I would do as well. First search for all the possible combinations, while returning only the keys. Show them in a combobox or list, and when the user makes a selection, go get the rest of the data.
     
  3. Ali

    Ali
    Newcomer

    Joined:
    Dec 16, 2002
    Messages:
    103
    Likes Received:
    3
    use the LIKE command, or use wildcards. ? for single charachter, * for multiple. eg:

    SELECT * FROM tblWobble WHERE wibble LIKE "ABC?"

    or something like that
     
  4. Diplo

    Veteran

    Joined:
    Apr 17, 2004
    Messages:
    1,474
    Likes Received:
    64
    Location:
    UK
    Actually, ANSI SQL states you use _ to match a single character and % to match multiple characters. I think it's only M$ Access that diverts from the standard.

    So, you would use

    SELECT * FROM Employee WHERE Name LIKE 'D%'

    This would return all rows from Employee table where name starts with 'D' and is followed by any number of other characters.
     
  5. Ali

    Ali
    Newcomer

    Joined:
    Dec 16, 2002
    Messages:
    103
    Likes Received:
    3
    Opps. Its been a while. I was doing it from memory. For some reason I thought it might have been ~ for a character search, but in the end just went with the good old DOS wildcards.

    Last database I used was MS SQL server, although my DB of choice has always been Postgress.
     
Loading...

Share This Page

  • About Us

    Beyond3D has been around for over a decade and prides itself on being the best place on the web for in-depth, technically-driven discussion and analysis of 3D graphics hardware. If you love pixels and transistors, you've come to the right place!

    Beyond3D is proudly published by GPU Tools Ltd.
Loading...