ALTER FUNCTION [dbo].[udf_CleanNonAlpha](@string VARCHAR(500)) RETURNS VARCHAR(500) AS BEGIN
DECLARE @clean VARCHAR(500);
WITH Clean AS ( SELECT REPLACE(@string, CHAR(255), ' ') AS col1 , 500 AS ch
UNION ALL
SELECT CASE WHEN CHAR(ch - 1) in ('Ö' , 'Ø' ) THEN REPLACE(col1, CHAR(ch - 1), 'OE') WHEN CHAR(ch - 1) in ('ö' , 'ø') THEN REPLACE(col1, CHAR(ch - 1), 'oe') ELSE col1 END ,ch - 1 FROM Clean WHERE ch > 1 )
SELECT @clean = col1 FROM Clean WHERE ch = 1
OPTION (MAXRECURSION 500);
RETURN @clean;
END
I want to remap the special characters with alternates specified.My issue here is the strings need to case sensitive which is not happening in my fnction. ex: both select dbo.[udf_zzCleanNonAlpha] ('Röss') select dbo.[udf_zzCleanNonAlpha] ('RÖss') produce 'ROEss'.Can someone please help me on this...
Update the case stmt as below SELECT CASE WHEN CHAR(ch - 1) in ('Ö' COLLATE SQL_Latin1_General_CP1_CS_AS, 'Ø' COLLATE SQL_Latin1_General_CP1_CS_AS) THEN REPLACE(col1, CHAR(ch - 1), 'OE') WHEN CHAR(ch - 1) in ('ö' COLLATE SQL_Latin1_General_CP1_CS_AS, 'ø' COLLATE SQL_Latin1_General_CP1_CS_AS) THEN REPLACE(col1, CHAR(ch - 1), 'oe')
Updated the case statments as below and worked.Thanks for giving the idea of using collate. WHEN CHAR(ch - 1) collate SQL_Latin1_General_CP1_Cs_AS IN ( 'Ö' , 'Ø' ) THEN REPLACE(col1, CHAR(ch - 1), 'OE') WHEN CHAR(ch - 1) collate SQL_Latin1_General_CP1_Cs_AS IN ('ö' , 'ø' ) THEN REPLACE(col1, CHAR(ch - 1), 'oe')
Case sensitive diacritics.
Posted - 01/29/2013 : 10:48:45
