Search string data with special characters removed

All we need is an easy explanation of the problem, so here it is.

We have a table with ~6 million of products:

ID Name Code
1 One 123.456.789-M
2 Two 852.789456
3 Three 1-123654.P

What is a good way (with regards to performance) to filter products by code column without masks (traces, slashes or dots)?

Example:

SELECT id, name FROM products WHERE code = '123456789M' OR code = '1123654P';

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Use an indexed computed column:

CREATE TABLE dbo.Products
(
    ID integer NOT NULL UNIQUE CLUSTERED,
    [Name] varchar(10) NOT NULL PRIMARY KEY NONCLUSTERED,
    Code varchar(20) NOT NULL,
    SearchCode AS 
        ISNULL(
            CONVERT(varchar(20), 
                REPLACE(
                    REPLACE(Code, '-', ''), 
                    '.', '')
                ), 
            ''),
    
    INDEX [IX dbo.Products SearchCode (Name)] 
        (SearchCode) 
        INCLUDE ([Name])
);

INSERT dbo.Products
    (ID, [Name], Code)
VALUES
    (1, 'One', '123.456.789-M'),
    (2, 'Two', '852.789456'),
    (3, 'Three', '1-123654.P');
SELECT P.ID, P.[Name]
FROM dbo.Products AS P
WHERE P.SearchCode = '123456789M';
ID Name
1 One

Search string data with special characters removed

db<>fiddle online demo

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply