Home How do I use COLLATE based on a function in T-SQL?
Reply: 0

How do I use COLLATE based on a function in T-SQL?

user1322
1#
user1322 Published in June 19, 2018, 6:14 pm

This question already has an answer here:

  • COLLATE in UDF does not work as expected 8 answers

I have the following function for setting COLLATE for the column @p based on the @c parameter. I was inspired by this answer.

CREATE FUNCTION [dbo].[fnsConvert]
(
    @p NVARCHAR(2000),
    @c NVARCHAR(2000)
)
RETURNS NVARCHAR(2000)
AS
    BEGIN
        IF ( @c = 'sv-SE' )
            SET @p = @p COLLATE Finnish_Swedish_100_CI_AS
        ELSE 
            SET @p = @p COLLATE SQL_Latin1_General_CP1_CI_AS
        RETURN @p    
    END

This is how I use it:

SELECT Title FROM Things
ORDER BY dbo.fnsConvert(Title, 'sv-SE')

Result: AÅÄBCÖ Expected: ABCÅÄÖ

In an attempt to debug it I run

DECLARE @l nvarchar(255)
DECLARE @P nvarchar(255)
EXEC @l = dbo.fnsConvert
    @p = Test, @c = 'sv-SE';

SELECT @l

This just return the string Test. I would expect it to also contain the COLLATE statement but I could very well be wrong about that.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.338145 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO