Make a T-SQL function case sensitive

Question

I have the following T-SQL function

FUNCTION dbo.UrlEncode(@url NVARCHAR(1024))
RETURNS NVARCHAR(3072)
AS
BEGIN
   DECLARE  @urlReturn NVARCHAR(3072)

   SET @urlReturn = REPLACE(@url,'Á','%c3%81') COLLATE Latin1_General_CS_AS  

   SET @urlReturn = REPLACE(@url,,'á','%c3%a1')   

    RETURN @urlReturn
END

But if I run:

SELECT dbo.UrlEncode('Áreá')

it returns

%c3%a1re%c3%a1

I need it to return

%c3%81re%c3%a1

Is there a way a can make the function case sensitive, I have tried COLLATE Latin1_General_CS_AS but without success

Thanks

score 1 · Accepted Answer · answered Apr 06 '15 at 21:16

You should specify the case sensitive collation next to the part to be replaced. Second REPLACE should then be applied on the result of the previous REPLACE:

DECLARE @urlReturn NVARCHAR(3072)
SET @urlReturn = REPLACE(@url ,'Á' COLLATE Latin1_General_CS_AS ,'%c3%81' )  

SET @urlReturn = REPLACE( @urlReturn ,'á','%c3%a1') 
SELECT @urlReturn

Make a T-SQL function case sensitive

1 Answers1