SQL Server: Find a specific word in all column in table

Question

i want to find a specific word in all columns of a specific table. i tried this sql select * from TestData where 'bisu' in (Name1,Name2,Name3) it works when column contain only the word bisu but if column contain text like Welcome bisu then my above sql fail.

i follow this below approach which worked but bit big. so anyone tell me how could i full fill my requirement just by normal SQL instead of calling store procedure.

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 

EXEC sp_FindStringInTable 'Irv%', 'Person', 'Address'

thanks

Is this something you need frequently or just as a one time thing for analysis? The performance of this type of thing is going to be downright awful. Is this a search type thing? If so, you would be FAR better off writing a query to handle the columns explicitly. — Sean Lange, Jul 01 '15 at 14:18
This question is a little bit more generic, but includes an answer for your problem: [search all tables, all columns for a specific value SQL Server](http://stackoverflow.com/q/1796506/44522) — MicSim, Jul 01 '15 at 14:26
This question has some answers you are looking for: http://stackoverflow.com/questions/27918807/sql-search-all-columns-of-a-table-for-text-value/30249132#30249132 — Brian Pressler, Jul 01 '15 at 14:29

score 0 · Answer 1 · answered Jul 01 '15 at 14:33

0

You should try following,

Select 
 *
From TestData
Where 
1 = case when name1 like '%bisu%' then 1 else 0 end 
And 1 = case when name2 like '%bisu%' then 1 else 0 end 
And 1 = case when name3 like '%bisu%' then 1 else 0 end

answered Jul 01 '15 at 14:33

Emipro Technologies Pvt. Ltd.

14,598
5
45
75

score 0 · Answer 2 · answered Jul 01 '15 at 14:44

0

just searching various answer from google and found a nice one and it worked.

select * from TestData
WHERE  Name1 + Name2 + Name3 like '%bisu%'

answered Jul 01 '15 at 14:44

Thomas

33,544
126
357
626

SQL Server: Find a specific word in all column in table

2 Answers2