?
Solved

sql searchterm

Posted on 2010-01-10
21
Medium Priority
?
281 Views
Last Modified: 2012-05-08
searching for
searchterm


want exact match

not
searchterms
searchterm1
declare @sql varchar(8000), @tbl varchar(255), @col varchar(255), 
@data varchar(50)
set @data = 'searchterm'
declare cur_tbl cursor for
      select a.name, b.name from sysobjects a, syscolumns b, 

systypes c where a.id = b.id and a.type = 'U' and c.xtype = 

b.xtype and c.name in ( 'varchar', 'nvarchar', 'text', 'ntext', 'int' )
open cur_tbl
fetch next from cur_tbl into @tbl, @col
while @@fetch_status = 0
begin
      set @sql = '
                        if exists (select * from [' + @tbl + '] where convert( 

varchar(255), [' + @col + '] ) like ''%' + @data + '%'')
                              select tbl=''' + @tbl + ''', col=''' + @col + ''', [' + 

@col + '], * from [' + @tbl + '] where convert( varchar(255), [' + 

@col + '] ) like ''%' + @data + '%''
                        '
      exec(@sql)

      fetch next from cur_tbl into @tbl, @col
end
close cur_tbl
deallocate cur_tbl

Open in new window

0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 3
  • 3
  • +2
21 Comments
 
LVL 3

Expert Comment

by:JohnSansom
ID: 26279341
Hi,

Please try changing the line:

@col + '] ) like ''%' + @data + '%''

TO

@col + '] ) like ''%' + @data + ' %''
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26279424
try

declare @sql varchar(8000), @tbl varchar(255), @col varchar(255), 
@data varchar(50)
set @data = 'searchterm'
declare cur_tbl cursor for
      select a.name, b.name from sysobjects a, syscolumns b, 

systypes c where a.id = b.id and a.type = 'U' and c.xtype = 

b.xtype and c.name in ( 'varchar', 'nvarchar', 'text', 'ntext', 'int' )
open cur_tbl
fetch next from cur_tbl into @tbl, @col
while @@fetch_status = 0
begin
      set @sql = '
                        if exists (select * from [' + @tbl + '] where convert( 

varchar(255), [' + @col + '] ) like ''' + @data + ''')
                              select tbl=''' + @tbl + ''', col=''' + @col + ''', [' + 

@col + '], * from [' + @tbl + '] where convert( varchar(255), [' + 

@col + '] ) like ''' + @data + '''
                        '
      exec(@sql)

      fetch next from cur_tbl into @tbl, @col
end
close cur_tbl
deallocate cur_tbl

Open in new window

0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26279434
Hi

I think what JohnSansom ment was:
(which is what I have done also)

JohnSansom:
Hi,

Please try changing the line:

@col + '] ) like ''%' + @data + '%''

TO

@col + '] ) like ''' + @data + ' ''

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:zadeveloper
ID: 26279448
Although Technically speaking you should change al instances of:

like ''%' + @data + '%''

to

= ''' + @data + '''

Open in new window

0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26279562
sorry, after reading the sql I think I understand what you want:

replace like ''%' + @data + '%''

with

like ''% ' + @data + ' %''

Open in new window

0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26279708
Try this

(I have redone some of the Sql)

declare @SelectStatement nvarchar(2000),
		@SqlStatement nvarchar(4000),
		@tbl sysname,
		@col sysname ,
		@data varchar(50)
	
set @data = 'searchterm'

declare cur_tbl cursor for
    select 
		a.name, b.name
	from 
		sysobjects a
		inner join syscolumns b on b.id = a.id 
		inner join systypes c on c.xtype = b.xtype and c.name in ( 'varchar', 'nvarchar', 'text', 'ntext', 'int' )
	where 
		a.type = 'U' 		
		
open cur_tbl
	fetch next from cur_tbl into @tbl, @col
	
	while @@fetch_status = 0 begin

		set @SelectStatement =	'select tbl=''' + @tbl + ''', col=''' + replace(@col, '''', '''''') + ''', [' + @col + '], * from ' +
								'[' + @tbl + '] where [' + @col + '] = ''' + @data + ''''
		
		set @SqlStatement = 'if exists (' + @SelectStatement + ') ' + @SelectStatement
				
		execute sp_executesql  @SqlStatement		
		fetch next from cur_tbl into @tbl, @col
	end
close cur_tbl
deallocate cur_tbl

Open in new window

0
 

Author Comment

by:rgb192
ID: 26279972
>>26279708

Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26280001
Change the = @data to like @data
0
 

Author Comment

by:rgb192
ID: 26280158
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'like'
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26280235
try this
declare @sql varchar(8000), @tbl varchar(255), @col varchar(255), 
@data varchar(50)
set @data = 'searchterm'
declare cur_tbl cursor for
      select a.name, b.name from sysobjects a, syscolumns b, 

systypes c where a.id = b.id and a.type = 'U' and c.xtype = 

b.xtype and c.name in ( 'varchar', 'nvarchar', 'text', 'ntext', 'int' )
open cur_tbl
fetch next from cur_tbl into @tbl, @col
while @@fetch_status = 0
begin
      set @sql = '
                        if exists (select * from [' + @tbl + '] where convert( 

varchar(255), [' + @col + '] ) = ''' + @data + ''')
                              select tbl=''' + @tbl + ''', col=''' + @col + ''', [' + 

@col + '], * from [' + @tbl + '] where convert( varchar(255), [' + 

@col + '] ) = ''' + @data + '''
                        '
      exec(@sql)

      fetch next from cur_tbl into @tbl, @col
end
close cur_tbl
deallocate cur_tbl

Open in new window

0
 
LVL 3

Expert Comment

by:JohnSansom
ID: 26281048
Hi,

If you want an exact match for a search term that is a complete term found anywhere whithin a string then please do the following:


0
 
LVL 3

Expert Comment

by:JohnSansom
ID: 26281094

declare @sql varchar(8000), @tbl varchar(255), @col varchar(255), 
@data varchar(50)
set @data = 'searchterm'
declare cur_tbl cursor for
      select a.name, b.name from sysobjects a, syscolumns b, 

systypes c where a.id = b.id and a.type = 'U' and c.xtype = 

b.xtype and c.name in ( 'varchar', 'nvarchar', 'text', 'ntext', 'int' )
open cur_tbl
fetch next from cur_tbl into @tbl, @col
while @@fetch_status = 0
begin
      set @sql = '
                        if exists (select * from [' + @tbl + '] where convert( 

varchar(255), [' + @col + '] ) like ''%' + @data + ' %'')
                              select tbl=''' + @tbl + ''', col=''' + @col + ''', [' + 

@col + '], * from [' + @tbl + '] where convert( varchar(255), [' + 

@col + '] ) like ''%' + @data + ' %''
                        '
      exec(@sql)

      fetch next from cur_tbl into @tbl, @col
end
close cur_tbl
deallocate cur_tbl

Open in new window

0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26281149
Modification made back to like.
The procedure will search for the columns that have the "whole word"  somewhere in the column.

declare @SelectStatement nvarchar(2000), 
                @SqlStatement nvarchar(4000), 
                @tbl sysname, 
                @col sysname , 
                @data varchar(50) 
         
set @data = 'searchterm' 
 
declare cur_tbl cursor for 
    select  
                a.name, b.name 
        from  
                sysobjects a 
                inner join syscolumns b on b.id = a.id  
                inner join systypes c on c.xtype = b.xtype and c.name in ( 'varchar', 'nvarchar', 'text', 'ntext', 'int' ) 
        where  
                a.type = 'U'             
                 
open cur_tbl 
        fetch next from cur_tbl into @tbl, @col 
         
        while @@fetch_status = 0 begin 
 
                set @SelectStatement =  'select tbl=''' + @tbl + ''', col=''' + replace(@col, '''', '''''') + ''', [' + @col + '], * from ' + 
                                                                '[' + @tbl + '] where [' + @col + '] like ''% ' + @data + ' %''' 
                 
                set @SqlStatement = 'if exists (' + @SelectStatement + ') ' + @SelectStatement 
                                 
                                 print @SqlStatement
                --execute sp_executesql  @SqlStatement             
                fetch next from cur_tbl into @tbl, @col 
        end 
close cur_tbl 
deallocate cur_tbl

Open in new window

0
 
LVL 13

Accepted Solution

by:
zadeveloper earned 2000 total points
ID: 26281150
and with the exec line commented back in :)
declare @SelectStatement nvarchar(2000), 
                @SqlStatement nvarchar(4000), 
                @tbl sysname, 
                @col sysname , 
                @data varchar(50) 
         
set @data = 'searchterm' 
 
declare cur_tbl cursor for 
    select  
                a.name, b.name 
        from  
                sysobjects a 
                inner join syscolumns b on b.id = a.id  
                inner join systypes c on c.xtype = b.xtype and c.name in ( 'varchar', 'nvarchar', 'text', 'ntext', 'int' ) 
        where  
                a.type = 'U'             
                 
open cur_tbl 
        fetch next from cur_tbl into @tbl, @col 
         
        while @@fetch_status = 0 begin 
 
                set @SelectStatement =  'select tbl=''' + @tbl + ''', col=''' + replace(@col, '''', '''''') + ''', [' + @col + '], * from ' + 
                                                                '[' + @tbl + '] where [' + @col + '] like ''% ' + @data + ' %''' 
                 
                set @SqlStatement = 'if exists (' + @SelectStatement + ') ' + @SelectStatement 
                                 
                execute sp_executesql  @SqlStatement             
                fetch next from cur_tbl into @tbl, @col 
        end 
close cur_tbl 
deallocate cur_tbl

Open in new window

0
 
LVL 7

Expert Comment

by:aplusexpert
ID: 26281672
Hi please use below SP.
ALTER PROC [dbo].[sp_SearchAndReplace]
(
	@SearchStr nvarchar(100),
	@ReplaceStr nvarchar(100) = NULL
)
-- Purpose: 	To search all columns of all tables for a string and replace it with another

AS
BEGIN


	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	DECLARE	@SQL nvarchar(4000), @RCTR int

	SET @RCTR = 0

	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')


	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				SET @SQL=	'UPDATE ' + @TableName + 
						' SET ' + @ColumnName 
						+ ' =  REPLACE(' + @ColumnName + ', ' 
						+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') + 
						') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				EXEC (@SQL)
				
				SET @RCTR = @RCTR + @@ROWCOUNT
			END
		END	
	END

	SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

Open in new window

0
 

Author Closing Comment

by:rgb192
ID: 31675262
works
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26287744
rgb192 - Did you verify my post? Can you comment on that?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26337551
rgb192 - expecting your response.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26340164
If I may respond.
Initally the question did ask that the entire column match the searchstring, however if you read on a little (and if you read the initial code and try to understand it ) it becomes clear that he ment the entire word in a string should be matched.
ie:
If the column data was "I like riding horses" and the search term was horse - this should not match as horse is not an exactly match of a whole word. Hence I sugessted the use of like with spaces surrounding the word as so ' %searchterm% '.
I understand that the initialy question may have been gramatically incorrect however this is true for many a question asked on this site. - and is bound to continue happening as we are dealing with a global community whom don't all speak engligh as a first language.
As you can see from my first comment - I undestood the question incorrectly as well (taking the %% out of the like statement effectivly make it a =)
Hope this is more clear.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question