/*
This stored procedure can be used to search and replace substring
in the char, nchar, varchar and nvarchar columns in ALL tables
in the current database. You should pass the text value to
search and the text value to replace. So, to replace all
char, nchar, varchar and nvarchar columns which contain
the substring 'Stas Jr.' with the substring 'StasJR', you can use the
following :
EXEC replace_substring @search_value = 'Stas Jr.',
@replace_value = 'StasJR'
*/
IF OBJECT_ID('replace_substring') IS NOT NULL DROP PROC replace_substring
GO
CREATE PROCEDURE replace_substring (
@search_value varchar(128) = null,
@replace_value varchar(128) = null
)
AS
DECLARE
@execstr varchar(1000),
@objectname sysname,
@colname sysname
SET NOCOUNT ON
IF @search_value IS NULL
BEGIN
RAISERROR ('You must specify the value to search', 16, 1)
RETURN
END
IF @replace_value IS NULL
BEGIN
RAISERROR ('You must specify the value to replace', 16, 1)
RETURN
END
DECLARE tb_fetch_cursor CURSOR FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tb_fetch_cursor
FETCH NEXT FROM tb_fetch_cursor INTO @objectname
WHILE (@@fetch_status <> -1)
BEGIN
DECLARE col_fetch_cursor CURSOR FOR
SELECT name FROM syscolumns WHERE id = OBJECT_ID(@objectname) AND type IN
(SELECT type FROM systypes WHERE name = 'char' OR name = 'nchar' OR name = 'varchar' OR name = 'nvarchar')
OPEN col_fetch_cursor
FETCH NEXT FROM col_fetch_cursor INTO @colname
WHILE (@@fetch_status <> -1)
BEGIN
SELECT @execstr = 'IF EXISTS (SELECT * FROM ' + @objectname + ' WHERE ' + @colname + ' like ''%' + @search_value + '%'') BEGIN SELECT ''' + @objectname + ''' as tbname, ''' + @colname + ''' as colname PRINT '''' UPDATE ' + @objectname + ' SET ' + @colname + ' = REPLACE(' + @colname + ',''' + @search_value + ''',''' + @replace_value + ''') WHERE ' + @colname + ' like ''%' + @search_value + '%'' END'
EXEC (@execstr)
FETCH NEXT FROM col_fetch_cursor INTO @colname
END
DEALLOCATE col_fetch_cursor
FETCH NEXT FROM tb_fetch_cursor INTO @objectname
END
DEALLOCATE tb_fetch_cursor
GO
stas wrote:
>Neil, yes, that's in the source data. I would appreciate your help. I never have enough time to learn more advanced TSQL. Thanks!
> ----- Original Message -----
> From: Neil Robertson-Ravo [Team Macromedia]
> To: SQL
> Sent: Tuesday, March 09, 2004 3:46 PM
> Subject: Re: DTS script question
>
>
> Is this in the data itself? This can be done in T-SQL easily....let me
> know if this is what you mean and we can work out a script.
>
> stas wrote:
>
> >I have a list of names and some of them have illegal (to my application)
> >characters. For example, I may have a first name as 'Stas Jr.", while I am
> >only looking for 'Stas' - however, I'd settle for just 'StasJR' as well. How
> >would I go about writing a script that would basically strip out any
> >non-alpha chars from any given script? I know how to do basic transformation
> >within a DTS transformation, is it just a matter of me picking up some
> >VBscript fast? I know this is a Regex job...
> >
> >Thanks
> >
> >
> >
> >
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
