SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Case sensitive diacritics.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

goldendays
Starting Member

3 Posts

Posted - 01/29/2013 :  10:48:45  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/29/2013 :  10:57:04  Show Profile  Reply with Quote
try to use case sensitive collation

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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 01/29/2013 :  10:59:19  Show Profile  Reply with Quote
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 - 01/29/2013 :  11:09:56  Show Profile  Reply with Quote
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 - 01/30/2013 :  05:59:31  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000