CODEWITHSUNDEEP
sql-server
Mou
Mou

Reputation: 16282

How to find specific word in all columns of a table

this is my table structure and data.

create table tbl_test
(id int identity(1,1),
column1 nvarchar(50),
coulmn2 nvarchar(50),
coulmn3 nvarchar(50))-- Create a table 

insert into tbl_Test (column1,coulmn2,coulmn3) values
('qqGriffrr','Serjey','Maciej')
insert into tbl_Test (column1,coulmn2,coulmn3) values('King','Fisher','Ajay')
insert into tbl_Test (column1,coulmn2,coulmn3) values('Paul','ssGriffdd','Serjey')
insert into tbl_Test (column1,coulmn2,coulmn3) values('King','Fisher','xxGriffzzz')

this way i am finding and it is working

select * from tbl_test where 'Griff'
IN(column1 ,column2 ,column3)

select * from tbl_test where column1 like '%Griff%'
OR coulmn2 like '%Griff%'
OR coulmn3 like '%Griff%'

i am looking some neat way hence i do not have to include so many OR and Like clause if no of column would increase. so tell me the best approach to solve it.

thanks

Upvotes: 0

Views: 527

Answers (4)

Mou
Mou

Reputation: 16282

I found one which looks good. here it is.

Searching and finding a string value in all columns in a SQL Server table

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

Calling this way

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

Upvotes: 0

harsh pareek
harsh pareek

Reputation: 61

select * 
from tbl_test
where column1+column2 +column3 like '%Griff%'

Upvotes: 0

Simone
Simone

Reputation: 1924

A little more generic, remove the external loop if you need only a single table:

DECLARE @TableName sysname, @ColName sysname
DECLARE @Find sysname
DECLARE @sql nvarchar(4000)
SET @Find = N'sometext'
DECLARE cTables CURSOR FOR SELECT name from dbo.sysobjects where Category = 0 AND type NOT IN (N'F', N'FN', N'IF', N'TF', N'P', N'TR', N'V', N'K') 
OPEN cTables
FETCH NEXT FROM cTables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE cColumns CURSOR FOR SELECT column_name FROM information_schema.columns WHERE table_name = @TableName AND DATA_TYPE NOT IN ('Image', 'bit', 'int', 'datetime', 'ntext', 'varbinary')

    OPEN cColumns
    FETCH NEXT FROM cColumns INTO @ColName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = N'SET NOCOUNT ON
DECLARE @Value sysname, @NewValue sysname
IF EXISTS (SELECT 1 FROM [' + @TableName + N'] WHERE [' + @ColName + N'] LIKE N''%'+ @Find + '%'')
BEGIN
    SELECT @Value = [' + @ColName + N'] FROM [' + @TableName + N'] WHERE [' + @ColName + N'] LIKE N''%' + @Find + '%''
    SELECT ''' + @ColName + N''' [Column], ''' + @TableName + N''' [Table], [' + @ColName + N'] [ValueFound], * FROM [' + @TableName + N'] WHERE [' + @ColName + N'] LIKE N''%' + @Find + '%''
END
'
--      PRINT @sql
        EXEC sp_sqlexec @sql

        FETCH NEXT FROM cColumns INTO @ColName
    END

    CLOSE cColumns;
    DEALLOCATE cColumns;

    FETCH NEXT FROM cTables INTO @TableName
END

CLOSE cTables;
DEALLOCATE cTables;

edited to use only selected table:

DECLARE @ColName sysname
DECLARE @Find sysname
DECLARE @sql nvarchar(4000), @TableName sysname
SET @Find = N'sometext'
SET @TableName = N'YourTable'

DECLARE cColumns CURSOR FOR SELECT column_name FROM information_schema.columns WHERE table_name = @TableName AND DATA_TYPE NOT IN ('Image', 'bit', 'int', 'datetime', 'ntext', 'varbinary')

OPEN cColumns
FETCH NEXT FROM cColumns INTO @ColName

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = N'SET NOCOUNT ON
DECLARE @Value sysname, @NewValue sysname
IF EXISTS (SELECT 1 FROM [' + @TableName + N'] WHERE [' + @ColName + N'] LIKE N''%'+ @Find + '%'')
BEGIN
    SELECT @Value = [' + @ColName + N'] FROM [' + @TableName + N'] WHERE [' + @ColName + N'] LIKE N''%' + @Find + '%''
    SELECT ''' + @ColName + N''' [Column], ''' + @TableName + N''' [Table], [' + @ColName + N'] [ValueFound], * FROM [' + @TableName + N'] WHERE [' + @ColName + N'] LIKE N''%' + @Find + '%''
END
'
--  PRINT @sql
    EXEC sp_sqlexec @sql

    FETCH NEXT FROM cColumns INTO @ColName
END

CLOSE cColumns;
DEALLOCATE cColumns;

Upvotes: 1

Max
Max

Reputation: 1902

If you have an ability to use full text search you can just do it like this:

select *
from test_table
where CONTAINS(*, '"blablabla"')

Upvotes: 3

Related Questions