Find Multiple Strings in Selected SQL Column


Problem:
Sometime you need to search one or more words, not coming in sequence from a column. Look the following example.
This is the Customer’s table.
Name
Address
Phone
Email
Pinter Bible
Lighthouse Promotions
PO Box 215
SPRINGVALE  VIC  8006
+1097897879123
Steve James
Avenida João Jorge, 112, ap. 31
Vila Industrial
Campinas - SP
13035-680
+9189898891828
Steve Brown
Av. Bellavista N° 185
Dep. 609
8420507
Recoleta
Santiago
+1989898089080

Requirement is to find all customers using “gmail” and having name as “steve”. The result would be as follow:
Name
Address
Phone
Email
Steve James
Avenida João Jorge, 112, ap. 31
Vila Industrial
Campinas - SP
13035-680
+9189898891828


Solution:


-- 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
After creating this stored procedure, we have to call it like:
EXECUTE dbo.sp_FindMultipleStringInTable 'gary gmail', dbo, 
contactdetails, 'Select * from contactdetails', 
'''Name'', ''Address'', ''Phone'', ''Email'''
and you'll get the desire results.

No comments:

Post a Comment