在选定列中查找多个字符串

引言

最近我遇到一个问题，我需要在表上应用一个过滤器，但条件与简单的 'like' 查询略有不同。

背景 

让我们尝试研究一下这个条件。假设我们有一个名为 'ContactDetails' 的表，包含以下列和记录

FirstName       LastName         Email
Gary            Lewis            gary@gmail.com
Natasha         Williams         natasha@gmail.com
Gary            James            gary@live.com

现在用户输入字符串 'gary gmail' 进行搜索；在这种情况下，SQL 存储过程应该只返回以下值

FirstName       LastName         Email
Gary            Lewis            gary@gmail.com

因为只有 gary 和 gmail 存在于任何列的记录中。

使用代码

为了执行此任务，我们需要在 SQL Server 上创建一个函数和一个存储过程。

1. 使用特定的分隔符分割可搜索的字符串。
2. 创建适当的 Select SQL，以在所需的列中搜索这些字符串。

所以，这是我用来使用特定分隔符分割字符串的第一个函数。

--
-- Here First @Sep variable use for separator and @s use for string which need to split   

CREATE FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))

RETURNS @t TABLE
    (
        val VARCHAR(MAX)
    )   
AS
    BEGIN
        DECLARE @xml XML
        SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'

        INSERT INTO @t(val)
        SELECT r.value('.','VARCHAR(MAX)') as Item
        FROM @xml.nodes('//root/r') AS RECORDS(r)

        RETURN
    END
GO

为了从它获取结果，我们可以简单地使用以下方式调用它

SELECT * FROM dbo.Split (' ', 'gary gmail')

这将产生以下结果

gary
gmail

现在我们在主存储过程中使用这个函数

-- Procedure for find the string in table
--@stringToFind  'string which need to find'
--@schema  'schema name' e.g. dbo
--@tableName  'table name'
--@SqlCommand  'sql command' 
--@SearchColumnName  'columns name; where string need to find'

CREATE PROCEDURE [dbo].[sp_FindMultipleStringInTable] 
@stringToFind VARCHAR(MAX), 
@schema sysname, 
@table sysname ,
@sqlCommand VARCHAR(MAX), 
@SearchColumnName VARCHAR(MAX)
AS
DECLARE @where VARCHAR(MAX)
DECLARE @columnName sysname
DECLARE @cursor VARCHAR(MAX)
DECLARE @searchString VARCHAR(MAX)
DECLARE @flg CHAR(1)
BEGIN TRY

SET @SearchColumnName =replace(@SearchColumnName,'"','''');
SET @sqlCommand =replace(@sqlCommand,'"','''');

SET @sqlCommand = @sqlCommand +' where '

SET @where = ''

DECLARE row_cursor CURSOR FOR 
SELECT * FROM dbo.split (' ',@stringToFind)

OPEN row_cursor 
FETCH NEXT FROM row_cursor INTO @searchString

WHILE @@FETCH_STATUS =0 
BEGIN 

SET @flg='O'
	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'') AND
	COLUMN_NAME IN ('+ @SearchColumnName +')'
	EXEC (@cursor)

	OPEN col_cursor
	FETCH NEXT FROM col_cursor INTO @columnName

	WHILE @@FETCH_STATUS = 0
	BEGIN
   
	IF @where <> '' AND @flg<>'O'
	BEGIN
		SET @where = @where + ' OR'  				
	END 
    SET @flg ='I'
   
	SET @where = @where + ' [' + @columnName + '] LIKE ''%' + @searchString + '%'''
  
	FETCH NEXT FROM col_cursor INTO @columnName
	END
	
	CLOSE col_cursor
	DEALLOCATE col_cursor
	SET @where = @where + ') AND ('
FETCH NEXT FROM row_cursor INTO @searchString
END

CLOSE row_cursor
DEALLOCATE row_cursor

SET @where =SUBSTRING(@where,0,(LEN(@Where)-5))
SET @sqlCommand = @sqlCommand + '('+@where+''

EXEC (@sqlCommand)


END TRY
BEGIN CATCH
IF CURSOR_STATUS('variable', 'col_cursor') <> -3
BEGIN
CLOSE col_cursor
DEALLOCATE col_cursor
END
END CATCH
GO

创建此存储过程后，我们需要像这样调用它

EXECUTE dbo.sp_FindMultipleStringInTable 'gary gmail', dbo, 
contactdetails, 'Select * from contactdetails', 
'''FirstName'',''LastName'',''Email'''

结果将是。

FirstName       LastName         Email
Gary            Lewis            gary@gmail.com