SQL Search - agnostic of spaces and dashes?

[zsouthboy]

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.

 

[Frank]

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.

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.

 

[Ali]

use the LIKE command, or use wildcards. ? for single charachter, * for multiple. eg:

SELECT * FROM tblWobble WHERE wibble LIKE "ABC?"

or something like that

 

[Diplo]

use the LIKE command, or use wildcards. ? for single charachter, * for multiple. eg:

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.

 

[Ali]

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.