Binaryworld - Case-sensitive replace for strings on case-insensitive server. ... [ SQL Server -> T-SQL ]

A sample script that demonstrates how to perform case-sensitive search & replace on a case-sensitive server. This script demonstrates some of the least known features of the T-SQL string functions.

Click here to copy the following block

DECLARE @SearchStr varchar( 30 ) , @SearchBin varbinary( 30 )
SELECT @SearchStr = 'AdministraciÃ“Ã³n' ,
    @SearchBin = CONVERT( varbinary( 30 ) , @SearchStr )
PRINT 'Original String : ' + @SearchStr
/*
Original String : AdministraciÃ“Ã³n
*/
SE @SearchStr = STUFF( @SearchBin ,
              CHARINDEX( CONVERT( binary(1) , 'Ã“' ) , @SearchBin ) ,
              1 , CONVERT( binary(1) , 'O') )
PRINT 'After Replace #1: ' + @SearchStr
/*
After Replace #1: AdministraciOn
*/
SELECT @SearchBin = CONVERT( varbinary( 30 ) , @SearchStr )
SELECT @SearchStr = STUFF( @SearchBin ,
              CHARINDEX( CONVERT( binary(1) , 'Ã³' ) , @SearchBin ) ,
              1 , CONVERT( binary(1) , 'o') )
PINT 'After Replace #2: ' + @SearchStr
/*
After Replace #2: AdministraciOon
*/

-- Mimic behavior of REPLACE using a LOOP
DECLARE @SearchStr varchar( 30 ) , @SearchBin varbinary( 30 ) ,
    @CharBin binary( 1 )
SELECT @SearchStr = 'AdministraciÃ“Ã³n de OperaciÃ³n'
SELECT @SearchBin = CONVERT( varbinary( 30 ) , @SearchStr )
SELECT @CharBin = CONVERT( binary(1) , 'Ã“' )
WHILE CHARINDEX( @CharBin , @SearchBin ) > 0
  SELECT @SearchBin = CONVERT( varbinary( 30 ) ,
           STUFF( @SearchBin ,
             CHARINDEX( @CharBin , @SearchBin ) ,
              1 , CONVERT( binary(1) , 'O') ) )

SELECT @CharBin = CONVERT( binary(1) , 'Ã³' )
WHILE CHARINDEX( @CharBin , @SearchBin ) > 0
  SELEC @SearchBin = CONVERT( varbinary( 30 ) ,
             STUFF( @SearchBin ,
             CHARINDEX( @CharBin , @SearchBin ) ,
              1 , CONVERT( binary(1) , 'o') ) )
SELECT @SearchStr = CONVERT( varchar( 30 ) , @SearchBin )
PRINT @SearchStr