SQL Server search index ignores special characters
I have a [nvarchar] column in a SQL Server table that contains data like 123456789, 123-456789, 1234.56.789, 1.23456-789, etc. Users just add dots, minus and spaces somewhere for readability and I don't know where.
Is there a way to create an index that ignores special characters and finds them when searching for the simple "123456789"?
+3
source to share
1 answer
There is no way to do what you want the way you want.
The best mechanism for this is using a computed column. It doesn't need to be indexed.
Initial position
CREATE TABLE YourTable
(
YourColumn NVARCHAR(50)
);
INSERT INTO YourTable
VALUES ('123456789'),
('123-456789'),
('1234.56.789'),
('1.23456-789');
Create a calculated column and index it.
ALTER TABLE YourTable
ADD CanonicalForm AS
CAST(REPLACE(REPLACE(REPLACE(YourColumn, '.', ''), '-', ''), ' ', '') AS NVARCHAR(50));
CREATE INDEX ix
ON YourTable(CanonicalForm)
INCLUDE (YourColumn);
Check him
SELECT *
FROM YourTable
WHERE CanonicalForm = '123456789'
Execution plan looks for an index
+1
source to share