CODEWITHSUNDEEP
sqlsql-servert-sqlcharacter-encoding
matt
matt

Reputation: 19

Function returns string and changes encoding

My database table looks like:

ID    | Name          | Name_Plain
1     | Łódź          |

What I want to achieve is, whenever there's insert or update into this table, I want SQL to automatically convert "Name" into text without Polish special characters and write it into "Name_Plan". So the result in the example above should be "Lodz".

I created a trigger and am now trying to code a user defined function which does the conversion from input text to output text.

ALTER FUNCTION [dbo].[usunogonki] (@t nvarchar(50))  RETURNS nvarchar(50)
AS 
BEGIN
    return @t
END;

The problem is.. the function above almost returns me the text I wanted! It returns "Lódz". I know it is something with the encoding, because Ł was converted into L and ó stays the same. What I would like from this function is to do exactly what I want, so for now it should return me unchanged text.

I tried some tricks with COLLATE procedure like

return (@t COLLATE sql_polish_cp1250_ci_as)

but with no success. What am I missing?

Upvotes: 0

Views: 186

Answers (1)

gotqn
gotqn

Reputation: 43636

Try this:

DROP TABLE IF EXISTS #TEST;

CREATE TABLE #TEST
(
    [ID] INT IDENTITY(1,1)
   ,[Name] NVARCHAR(12)
   ,Name_PLain AS CAST([Name] AS VARCHAR(12)) COLLATE SQL_Latin1_General_CP1253_CI_AI 
);


INSERT INTO #TEST ([Name])
VALUES (N'Łódź')

SELECT *
FROM #TEST

enter image description here

creating a computed column which you can materialized if you want. Or basically, to stick with your example:

return (@t COLLATE SQL_Latin1_General_CP1253_CI_AI)

Upvotes: 1

Related Questions