sql searchterm
searching for
searchterm
want exact match
not
searchterms
searchterm1
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
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
Hi
I think what JohnSansom ment was:
(which is what I have done also)
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 + ' ''
Although Technically speaking you should change al instances of:
like ''%' + @data + '%''
to
like ''%' + @data + '%''
to
= ''' + @data + '''
sorry, after reading the sql I think I understand what you want:
replace like ''%' + @data + '%''
with
replace like ''%' + @data + '%''
with
like ''% ' + @data + ' %''
Try this
(I have redone some of the Sql)
(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_tblASKER
>>26279708
Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator.
Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator.
Change the = @data to like @data
ASKER
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'like'
Incorrect syntax near the keyword 'like'
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
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:
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:
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
Modification made back to like.
The procedure will search for the columns that have the "whole word" somewhere in the column.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
works
rgb192 - Did you verify my post? Can you comment on that?
rgb192 - expecting your response.
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.
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.
Please try changing the line:
@col + '] ) like ''%' + @data + '%''
TO
@col + '] ) like ''%' + @data + ' %''