搜尋刪除了特殊字元的字元串數據

我們有一張包含約 600 萬種產品的表格：

什麼是不帶遮罩（跡線、斜線或點）按程式碼列過濾產品的好方法（就性能而言）？

例子：

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

使用索引計算列：

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';

db<>fiddle 線上展示

引用自：https://dba.stackexchange.com/questions/291876