| Author |
 Topic  |
|
|
buck1107
Starting Member
6 Posts |
 Posted - 09/13/2012 : 17:25:31
|
Good Afternoon,
I am working on a Stored Procedure to replace a string in all tables. When I run a Select statement, I get matches with my search string; however, when I run the Update statement, none of the aforementioned matches are replaced.
Here is the search string: ">%http%<!--
(the opening double quote is part of the string, and the % are wildcards).
Any help or suggestions is greatly appreciated.
Thanks!
Below is my code for the Stored Procedure I'm using:
USE [cop]
GO
/****** Object: StoredProcedure [dbo].[SearchAllTablesWildcard_Replace2] Script Date: 09/13/2012 15:32:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SearchAllTablesWildcard_Replace2]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL NVARCHAR(4000)
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = '%' + @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 + ',''' + @SearchStr2 + ''','+''''')' +
' WHERE ' + @ColumnName + ' LIKE ''' + @SearchStr2 + ''''
--PRINT(@SQL )
EXEC(@SQL)
END
END
END
END
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 09/13/2012 : 17:34:56
|
| You can't use wildcards in the REPLACE function. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47037 Posts |
Posted - 09/13/2012 : 17:40:20
|
one way to do is using STUFF and PATINDEX
STUFF(yourcolumn,PATINDEX('your pattern here',youcolumn),LEN('your pattern'),'')
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 09/13/2012 : 17:42:00
|
| How would you get the LEN from a pattern with wildcards? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47037 Posts |
Posted - 09/13/2012 : 17:56:30
|
if patterns start and end is fixed you can still get it
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
buck1107
Starting Member
6 Posts |
Posted - 09/24/2012 : 16:01:22
|
quote:
Originally posted by visakh16
if patterns start and end is fixed you can still get it
Can the patten in PATINDEX actually open without a wildcard character, so that the start and end are fixed? I've tried
PATINDEX('">%http%<!--', AcademicDishonestyDesc)
but it only finds matching patterns, if there is nothing preceding or following the pattern in the string.
Thanks! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47037 Posts |
Posted - 09/24/2012 : 16:42:53
|
sorry i didnt get you.
can you illustrate with some sample data?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
buck1107
Starting Member
6 Posts |
Posted - 09/24/2012 : 18:21:26
|
quote:
Originally posted by visakh16
sorry i didnt get you.
can you illustrate with some sample data?
Thanks,
If I want to look for a string that always begins with and ends with the same characters, and if there is the potential for more data before or after the search pattern, how would it be possible to search for the 'fixed' beginning and ending, e.g.
sample: ">corvettes<http://www.corvette.com>corvettes<!--
sample search: ">%http%<!--
The double quote is a part of the search string.
Thanks for your help. |
|
|
|
buck1107
Starting Member
6 Posts |
Posted - 09/25/2012 : 13:31:42
|
quote:
Originally posted by visakh16
if patterns start and end is fixed you can still get it
Maybe I can clarify my question further: how do I get the patterns start and end is fixed, as stated above?
Many thanks. |
|
|
| |
Topic |
|
Replace on All Tables - Stored Procedure
