MYSQL — Number formatting issue

I'm working on a gig right now where the client wants the user to be able to search for a product by product code.

A product code is formatted like so: 123.4567.89

So, the search box should return that product whether the user enters the number with the periods, without the periods, or with spaces.

So, all of the following should return the product: 123.4567.89, 123456789, 123 4567 89.

My current query looks like so:

SELECT      *
FROM        products
WHERE       product_code LIKE '%$search_code%'"

I'm at a loss as to how I would revise that to include all the different possibilities of how a user would 开发者_开发知识库input these numbers.

Thanks in advance for any help.

---

1. [Front End] Limit the characters the user can enter. Only allow periods and spaces. Don't allow any alpha characters (if all your product SKUs are numerical).

2. [Middle Tier] After the form is posted, double check the data for extraneous characters on the back end. If somehow the client managed to bipass the validation on the front end, you can catch it on the back end. Use a simple search and replace in your language of choice.

3. [Database/Back-End] Once the data is restricted to only numeric digits and you send the SKU to your database query, strip out all periods on your products table. If you know you only use periods to store the SKUs, just search excluding them, e.g.

SELECT *
FROM products
WHERE REPLACE(product_code,'.','') = @productCode

Avoid wildcard %% searches, they're expensive.

---

You have to normalize the number input by the user before doing the search, that is: make it have the same format as the number stored in the database.

For instance, if the numbers are stored in the database without the periods (like 123456789), you have to pre-process the number input by the user to also remove the periods, spaces and any other characters from it.

Edit: if the numbers are stored in the database with the periods, than you also need to normalize them by removing the periods as @HertzaHaeon pointed in his answer.

---

How about removing dots and spaces in both the database code and the searcg input, so you have just the digits? Something like this:

WHERE REPLACE(product_code, '.', '') LIKE '%formatted_search_code%'

For the search input, you can strip everything but digits form it with a regular expression or simple substring replacement.

---

I think the best solution to your problem is to format the search value so that is matches the format used in your database. But than you will only find the product, if the user fills in the whole product number. If this is not the desired solution and you want to be able to let the user fill in any part of a product code and find al the products that have a code containing that that I think you should filter out the periods in your database.

The fasted solution would be to do it actually in your database. Remove the dots from your product code or add an extra field containing the product code without dots. This will speed up the query when the dataset gets larger.

If you not want to do that you can always filter out the dots in the search query:

REPLACE(product_code,'.','') LIKE '%$search_code%'

This will do the thrick but can be very slow when the dataset gets bigger.

---

I work with this all the time with social-security numbers. My solution is to take your input string, strip out and characters that are not digits, make sure that the string is the proper length and then use the substring function to break the string up and then put it back together with the delimiters. If you're using PHP, the function might look like this this:

<?php
function FormatProductCode($String) {
    $String = preg_replace("/[^0-9]/", "", $String);
    if ($String == "") {
        return null;
    }
    $String = str_pad($String, 9, "0", STR_PAD_LEFT);
    return substr($String, 0, 3) . "." . substr($String, 3, 4) . "." . substr($String, 7, 2);
}
?>

Use this function any time that you need to input data into the database or compare data.