Binaryworld - Generic logic to search & replace characters in a string. ... [ SQL Server -> T-SQL ]

A generic logic to search & replace certain characters in a string. This approach is flexible in the sense that new characters can be added for searching without modifying the logic. This will make the code more maintainable too.

Click here to copy the following block

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