Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
goldendays
Starting Member
3 Posts |
Posted - 2013-01-29 : 10:48:45
|
ALTER FUNCTION [dbo].[udf_CleanNonAlpha](@string VARCHAR(500))RETURNS VARCHAR(500)ASBEGINDECLARE @clean VARCHAR(500);WITH Clean AS(SELECT REPLACE(@string, CHAR(255), ' ') AS col1 , 500 AS chUNION ALLSELECT 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 - 1FROM CleanWHERE ch > 1)SELECT @clean = col1FROM CleanWHERE ch = 1OPTION (MAXRECURSION 500);RETURN @clean;ENDI 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... |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 10:57:04
|
try to use case sensitive collation------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-29 : 10:59:19
|
Explicitly specify a case sensitive (and accent sensitive) collation in your case expressions - for example:....WHEN CHAR(ch - 1) in ('Ö' COLLATE SQL_Latin1_General_CP1_CS_AS, 'Ø' COLLATE SQL_Latin1_General_CP1_CS_AS) .... |
|
|
goldendays
Starting Member
3 Posts |
Posted - 2013-01-29 : 11:09:56
|
Update the case stmt as belowSELECT 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') ELSE col1 ENDNow both the results show up as Roess. |
|
|
goldendays
Starting Member
3 Posts |
Posted - 2013-01-30 : 05:59:31
|
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') |
|
|
|
|
|
|
|