SQL UDF works on string and does not work on column

Question

I have a function for converting UTF8 characters. When I call the function using string it works as expected.

select dbo.fn_PolskieZnaki_utf8('KrakÃ³w')

returns Kraków

But when I select a column from the table it does not work. It returns KrakÃ³w. What is wrong?

use MyDataBase 
GO
select dbo.fn_PolskieZnaki_utf8(MyColumn)
from MyTable

Here is the function itself:

CREATE FUNCTION [dbo].[fn_PolskieZnaki_utf8](@string NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
begin
    RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
                 (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    (@string,   
'Ä,,',  N'Ą'),  
'Ä...', N'ą'),  
'Ä†',   N'Ć'),  
'Ä‡',   N'ć'),  
'Ä˜',   N'Ę'),  
'Ä™',   N'ę'),  
'Å⁯',   N'Ł'),  
'Å,',   N'ł'),  
'Åƒ',   N'Ń'),  
'Å„',   N'ń'),  
'Ã"',   N'Ó'),  
'Ã³',   N'ó'),  
'Åš',   N'Ś'),  
'Å›',   N'ś'),  
'Å¹',   N'Ź'),  
'Å°',   N'ź'),  
'Å»',   N'Ż'),  
'Å¼',   N'ż')   
end

what is your `MyTable` definition? – ughai May 14 '15 at 11:19 — ughai, May 14 '15 at 11:19
@ughai I simply write dbo.MyTableName – Przemyslaw Remin May 14 '15 at 13:09 — Przemyslaw Remin, May 14 '15 at 13:09

score 2 · Accepted Answer · edited May 23 '17 at 10:28

This may be because of collation of the column in your table.

Check this Sample Query.

DECLARE @table table(c varchar(50) collate SQL_Polish_Cp1250_CI_AS )
insert into @table values(N'KrakÃ³w')

DECLARE @table2 table(c varchar(50) collate SQL_Latin1_General_Cp1_CS_AS )
insert into @table2 values(N'KrakÃ³w')

select dbo.fn_PolskieZnaki_utf8('KrakÃ³w'),dbo.fn_PolskieZnaki_utf8(c) from @table
select dbo.fn_PolskieZnaki_utf8('KrakÃ³w'),dbo.fn_PolskieZnaki_utf8(c) from @table2

Output:

Kraków  KrakA3w

Kraków  Kraków

EDIT In addition to the above, In your function when using REPLACE you are missing N before the search character 'Ä,,', N'Ą'). It should be like this N'Ä,,', N'Ą').

The updated function will look like this.

ALTER FUNCTION [dbo].[fn_PolskieZnaki_utf8](@string NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
begin
    RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
                 (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    (@string,   
N'Ä,,',  N'Ą'),  
N'Ä...', N'ą'),  
N'Ä†',   N'Ć'),  
N'Ä‡',   N'ć'),  
N'Ä˜',   N'Ę'),  
N'Ä™',   N'ę'),  
N'Å⁯',   N'Ł'),  
N'Å,',   N'ł'),  
N'Åƒ',   N'Ń'),  
N'Å„',   N'ń'),  
N'Ã"',   N'Ó'),  
N'Ã³',   N'ó'),  
N'Åš',   N'Ś'),  
N'Å›',   N'ś'),  
N'Å¹',   N'Ź'),  
N'Å°',   N'ź'),  
N'Å»',   N'Ż'),  
N'Å¼',   N'ż')   
end

Additional Links:

COLLATE

Find Collation of Database and Table

What is the meaning of the prefix N in T-SQL statements?

I have tried `SQL_Polish_Cp1250_CI_AS` and `Polish_CI_AS`. Neither of them works. — Przemyslaw Remin, May 14 '15 at 13:10

score 0 · Answer 2 · answered May 15 '15 at 07:53

Finally this function worked for me. I have changed the characters. N before the character is necessary. The collate turned out not to be necessary. In comments I give some Polish cities using those bushy characters.

CREATE FUNCTION [dbo].[fn_PolskieZnaki_UTF8](@string NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
begin
    RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
                 (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    (@string,   
N'Ä„',   N'Ą'),  --RUDA ŚLÄ„SKA
N'Ä…',   N'ą'),  --ŚlÄ…skie  
N'Ä†',   N'Ć'),  --ZAMOĹšÄ†
N'Ä‡',   N'ć'),  --ZamoĹ›Ä‡
N'Ä',    N'Ę'),  --ŁÄCZNA
N'Ę™',   N'ę'),  --Ostrowiec ŚwiĘ™tokrzyski
N'Ĺ⁯',    N'Ł'),  --BIAĹA PODLASKA
N'Ĺ‚',   N'ł'),  --MirosĹ‚awiec
N'Ĺ',    N'Ń'),  --POZNAĹ
N'Ĺ„',   N'ń'),  --PoznaĹ„
N'Ă“',   N'Ó'),  --PIWNICZNA-ZDRĂ“J
N'Ăł',   N'ó'),  --Piwniczna-ZdrĂłj
N'Ĺš',   N'Ś'),  --ĹšlÄ…skie
N'Ĺ›',   N'ś'),  --DolnoĹ›ląskie
N'Ĺą',   N'Ź'),  --CZELADĹą
N'Ĺş',   N'ź'),  --ŁódĹş
N'Ĺ»',   N'Ż'),  --CHODZIEĹ»
N'ĹĽ',   N'ż')   --ŁomĹĽa
end

SQL UDF works on string and does not work on column

2 Answers2