sql searchterm

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

LVL 1
rgb192Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnSansomCommented:
Hi,

Please try changing the line:

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

TO

@col + '] ) like ''%' + @data + ' %''
0
zadeveloperCommented:
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
zadeveloperCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

zadeveloperCommented:
Although Technically speaking you should change al instances of:

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

to

= ''' + @data + '''

Open in new window

0
zadeveloperCommented:
sorry, after reading the sql I think I understand what you want:

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

with

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

Open in new window

0
zadeveloperCommented:
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
rgb192Author Commented:
>>26279708

Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator.
0
zadeveloperCommented:
Change the = @data to like @data
0
rgb192Author Commented:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'like'
0
SharathData EngineerCommented:
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
JohnSansomCommented:
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
JohnSansomCommented:

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
zadeveloperCommented:
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
zadeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aplusexpertCommented:
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
rgb192Author Commented:
works
0
SharathData EngineerCommented:
rgb192 - Did you verify my post? Can you comment on that?
0
SharathData EngineerCommented:
rgb192 - expecting your response.
0
zadeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.