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?
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?
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.
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