Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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.
 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
52326 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

3873 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  
 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.19 seconds. Powered By: Snitz Forums 2000