Using fulltext search to find a sequence of digits We have a column of ten digit numbers (with leading zeros). We are trying to find rows that have a specific sequence of digits.

For example, all rows containing "5088" in any place in the column.

Can full text search (FTS) help us in this case? 

We tried other methods such as LIKE, but the performance was very poor.
FTS is language specific; it can help you to find stuff in SQL Server which is specific to a language rather than treating it just like any other string in the databases. 

5088 is a base 10 number, I don’t think FTS will be of much help here. I think you’re only option is to use the LIKE operator. 

To learn more about why your query is running slow with like operator read Sargability: Why %string% Is Slow article by Brent Ozar.  



Alternatively, if you always look for a specific value in the column in question, you can make use of the Computed columns, something like... 

Test Table

CREATE TABLE #Test (ID INT , Value VARCHAR(100))
GO

INSERT INTO #Test VALUES (1 , 'ABC'), (2 , '') , (3 , 'EFG')
                       , (4 , 'A123BC'), (1 , 'AB123C') , (1 , 'ABC123') 
GO


Let's say you are only interested in the rows where it has a value of 123 and this the only value you always check for, then you can add another column (Computed) to the table, something like... 

ALTER TABLE #Test
ADD IsValid  AS (CASE WHEN Value LIKE '%123%' THEN 1 ELSE 0 END)
GO


And probably index the column too, something like....

CREATE INDEX NIX_IsValid_Record 
ON #Test (IsValid)


Now your query for records with 123 string should be... 

Select * from #Test Where isValid = 1


Even though you are looking for records with a string 123 anywhere in there but you will benefit from the Index on isValid column.