Best technique to search for matching substrings with Linq & SQL Server

Question

I have a requirement to find rows in a table containing 200,000 entries. Some may not consider this 'large', but it is large enough to warrant performance considerations.

The table contains strings consisting of digits only. For instance, the user can enter something like '12340-0560-78', or portions of this, e.g. '0560', and I need to match values

12345678 and 123405678 and 0123456780 etc

These are NDCs, national drug codes, and in spite of the standard, manufacturers format them in a variety of ways by adding or omitting zeros in various positions in their barcodes.

I started out with letting Linq do the work, by removing zeros and non-numeric characters from the search string and using Contains() on the column after removing all zeros from it. This is too slow.

So I added a computed column to the table, containing the search column minus all zeros. This is better, but I am still doing a table scan due to the Contains().

I then created a full text index, but realized that with a full text index I cannot search for substrings, only for words, phrases, and prefixes. Strange, but it does not solve this problem.

Are there other options?

Answer 1

How about just creating a simple clustered index on the computed column. Is the performance ok then?

e.g.

CREATE TABLE [dbo].[foo](
    [code] [varchar](20) NULL,
    [ccol]  AS (replace(replace([code],'-',''),' ','')
) ON [PRIMARY]

and

CREATE CLUSTERED INDEX [IX_foo] ON [dbo].[foo] 
(
    [ccol] ASC
)
...