SQL search across multiple columns in any order

Question

My users are trying to find records in my SQL db by providing simple text strings like this:

SCRAP 000000152 TMB-0000000025

These values can be in any order and any may be excluded. For example, they may enter:

SCRAP TMB-0000000025 SCRAP 000000152 SCRAP SCRAP 000000152 TMB-0000000025 000000152

All should work and include the same record as the original search, but they may also contain additional records because fewer columns are used in the match.

Here is a sample table to use for the results:

DECLARE @search1 varchar(50) = 'SCRAP 000000152 TMB-0000000025'
DECLARE @search2 varchar(50) = 'SCRAP'
DECLARE @search3 varchar(50) = 'TMB-0000000025 SCRAP'
DECLARE @search4 varchar(50) = '000000152 SCRAP'
DECLARE @search5 varchar(50) = 'SCRAP 000000152'
DECLARE @search6 varchar(50) = 'TMB-0000000025 000000152'

DECLARE @table TABLE (WC varchar(20),WO varchar(20),PN varchar(20))
INSERT INTO @table
SELECT 'SCRAP','000000152','TMB-0000000025' UNION
SELECT 'SCRAP','000012312','121-0000121515' UNION
SELECT 'SM01','000000152','121-0000155' UNION
SELECT 'TH01','000123151','TMB-0000000025'

SELECT * FROM @table

One additional wrinkle, the user does not have to enter 000000152, they can enter 152 and it should find the same results.

I can use patindex, but it requires the users to enter the search terms in a specific order, or for me to have an exponentially larger string to compare as I try to put them in all possible arrangements.

What is the best way to do this in SQL? Or, is this outside the capabilities of SQL? It is quite possible that the table will have well over 10,000 records (for some instances even over 100,000), so the query has to be efficient.

Answer 1

You can try this SP:

USE master 
GO 

CREATE PROCEDURE sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname 
AS 

DECLARE @sqlCommand VARCHAR(8000) 
DECLARE @where VARCHAR(8000) 
DECLARE @columnName sysname 
DECLARE @cursor VARCHAR(8000) 

BEGIN TRY 
   SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE' 
   SET @where = '' 

   SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME 
   FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = ''' + @schema + ''' 
   AND TABLE_NAME = ''' + @table + ''' 
   AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')' 

   EXEC (@cursor) 

   OPEN col_cursor    
   FETCH NEXT FROM col_cursor INTO @columnName    

   WHILE @@FETCH_STATUS = 0    
   BEGIN    
       IF @where <> '' 
           SET @where = @where + ' OR' 

       SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + '''' 
       FETCH NEXT FROM col_cursor INTO @columnName    
   END    

   CLOSE col_cursor    
   DEALLOCATE col_cursor  

   SET @sqlCommand = @sqlCommand + @where 
   --PRINT @sqlCommand 
   EXEC (@sqlCommand)  
END TRY 
BEGIN CATCH 
   PRINT 'There was an error. Check to make sure object exists.' 
   IF CURSOR_STATUS('variable', 'col_cursor') <> -3 
   BEGIN 
       CLOSE col_cursor    
       DEALLOCATE col_cursor  
   END 
END CATCH

This will have results as follow:

USE AdventureWorks 
GO 
EXEC sp_FindStringInTable 'Irv%', 'Person', 'Address'

USE AdventureWorks 
GO 
EXEC sp_FindStringInTable '%land%', 'Person', 'Address'

That's all there is to it. Once this has been created you can use this against any table and any database on your server.(Read More)

Answer 2

Agree with @MitchWheat (as usual). This database is not designed for queries like that, nor would any kind of "basic query" help. Best way would be to build a list of strings appearing in any column of the database, mapped back to the source column and row, and search that lookup table for your strings. This is pretty much what Lucene and any other full-text search library will do for you. SQL has a native implementation, but if the pros say go with a third party implementation, I'd say it's worth a look-see.