CREATE TABLE #ReplChars (
LookFor char( 1 )  ,
ReplaceWith char( 1 ) DEFAULT ( '_' )
)
GO
INSERT INTO #ReplChars VALUES( '/' , DEFAULT );
INSERT INTO #ReplChars VALUES( '*' , DEFAULT );
INSERT INTO #ReplChars VALUES( '?' , DEFAULT );
GO

/* SQL6x version using the STUFF function */
DECLARE @Str varchar(30 )
SELECT @Str = 'abc.07/07/2000*x?'
WHILE( EXISTS( SELECT * FROM #ReplChars
              WHERE CHARINDEX( LookFor , @Str ) > 0 ) )
    SELECT @Str = STUFF( @Str , CHARINDEX( LookFor , @Str ) , 1 , ReplaceWith )
    FROM #ReplChars
    WHERE CHARINDEX( LookFor , @Str ) > 0
PRINT @Str


/* SQL70 version queries using the new REPLACE function */
DECLARE @Str varchar(30 )
SELECT @Str = 'abc.07/07/2000*x?'
WHILE( EXISTS( SELECT * FROM #ReplChars
              WHERE CHARINDEX( LookFor , @Str ) > 0 ) )
    SELECT @Str = REPLACE( @Str , LookFor , ReplaceWith )
    FROM #ReplChars
    WHERE CHARINDEX( LookFor , @Str ) > 0
PRINT @Str

-- Or the T-SQL update extension
SELECT @Str = 'abc.07/07/2000*x?'
UPDATE #ReplChars
SET @Str = REPLACE( @Str , LookFor , ReplaceWith )
WHERE CHARINDEX( LookFor , @Str ) > 0
PRINT @Str

DROP TABLE #ReplChars

This page was last updated on May 01, 2006 04:28 PM.