sandbo00 sandbo00 - 1 year ago 98
SQL Question

How to replace an umlaut inside a T-SQL function

I need to replace umlauts in a function i defined in T-SQL.
My code looks like this:

CREATE FUNCTION [dbo].[my_function](@s varchar(4000))
RETURNS varchar(4000)
AS
BEGIN
set @s = upper(@s)
set @s = replace(@s,'Ä','AE')
return @s
END


When I input
äpfel
I get
AEPFEL
, which is my expected result,
but when I input
apfel
I also get
AEPFEL
, which is not supposed to happen.

I'm pretty sure that this is a matter of accent-sensitive collation, but I have no idea how to use that with the replace function.

Any input is appreciated!

Answer Source

You can specify a collation explicitly per string expression:

set @s = replace(@s COLLATE Latin1_General_CI_AS, 'Ä', 'AE')

And after this REPLACE will take accents into account. Note that you can't put the COLLATEon the parameter or the return value, unfortunately, which would be a more obvious solution.