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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Case sensitive diacritics.

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)
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...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-29 : 10:57:04
try to use case sensitive collation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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)
....
Go to Top of Page

goldendays
Starting Member

3 Posts

Posted - 2013-01-29 : 11:09:56
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')


ELSE col1 END

Now both the results show up as Roess.
Go to Top of Page

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')
Go to Top of Page
   

- Advertisement -