Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql searchterm

Posted on 2010-01-10
21
Medium Priority
?
285 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
  • 9
  • 3
  • 3
  • +2
19 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline

581 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