Using fulltext search to find a sequence of digits

Question

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.

Answer 1

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.

Answer 2

A Wild Approach to SQL Server Full Text Wildcards

Prefix wild cards are not (natively) supported by SQL Server versions for full text indexing up to the present. Perhaps this will arrive in the future.

But with a little code you can get some interesting results.

Full Text Indexing stores exist in SQL Server This stores each unique string in the SQL Server for that full text index. The unique strings become the corpus that can be searched for the full text index in use.

Please note that features such as stopwords, stoplists, thesaurus, etc may limit the strings returned.

However, since the candidate tokens are stored in SQL Server you can harvest the strings that match your wildcard from the sys.dm_fts_index_keywords view.

For example, the sample code I posted a few years ago in the MSDN forum:

DECLARE @SearchText NVARCHAR(1000)
SET @SearchText = ''

-- Collect strings that match the wildcard within the full-text corpus.
-- For example only one instance of 'chocolate' is in the corpus, but     
-- There may be many full text strings with 'chocolate' in them.
-- The matching strings can be strung together with ' OR ' to 
-- create an indirect search for the wildcard.

SELECT @SearchText = @SearchText + display_term + ' OR ' 
FROM sys.dm_fts_index_keywords ( DB_ID('DBName'),
     object_id('TableName') )
WHERE display_term like '%12345%'

-- My search for '%12345%' returned two criteria "ABC12345XYZ" and "123456"
-- Now the strings that match your wildcard in the stored full text index
-- can be searched for the texts that contain your wildcard(s). 

SELECT @SearchText = LEFT (@SearchText, LEN(@SearchText) - 3)-- Trim the last ' OR '

-- Now this query will return all texts containing "ABC12345XYZ OR 123456"

SELECT * FROM TableName
WHERE CONTAINS(*, @SearchText)

-- The select returns the texts with "ABC12345XYZ OR 123456" included.

In this case the OR of candidate tokens provides the full list of non-wildcarded strings to Full Text Search. The performance is based on the ability of the function to return those values.

Note: This is stretching the purpose of Full Text Search, but within its limitations it may at times be useful. Also, you should review the behaviors of numeric values in SQL Server Full Text Indexing.

Answer 3

Using PostgreSQL

PostgreSQL makes this easy with pg_trgm. 1,000,000 rows of sample data fitting your description.

CREATE TABLE foo
AS
  SELECT lpad((random()*1e10)::text, 10, '0')::text AS x
  FROM generate_series(1,1e6) AS gs;

CREATE EXTENSION pg_trgm;
CREATE INDEX ON foo USING GIN (x gin_trgm_ops);

Now to find the string %4242%

EXPLAIN ANALYZE SELECT * FROM foo WHERE x LIKE '%4242%';
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=36.77..397.22 rows=100 width=11) (actual time=1.844..3.653 rows=651 loops=1)
   Recheck Cond: (x ~~ '%4242%'::text)
   Rows Removed by Index Recheck: 588
   Heap Blocks: exact=1114
   ->  Bitmap Index Scan on foo_x_idx  (cost=0.00..36.75 rows=100 width=0) (actual time=1.596..1.596 rows=1239 loops=1)
         Index Cond: (x ~~ '%4242%'::text)
 Planning time: 0.247 ms
 Execution time: 3.741 ms
(8 rows)

3.7ms to search through 1,000,000 ROWS... That's a 42MB table, and 30MB index... GOOD LORD THAT'S FAST. I'm glad I use PostgreSQL.

And, you can likely make it even faster if you remove the leading '0's.

Non-PostgreSQL

SQL Server does not provide trigram indexes.,

PostgreSQL's extensive support for very clever indexes, such as range type indexes and trigram indexes, makes it orders of magnitude faster than MS SQL Server for a certain class of operations. But only if you know how to use those features properly.

Nevertheless, they can be efficiently implemented as shown in Trigram Wildcard String Search in SQL Server by Paul White.