Why charactor '£' will be converted to "L" when update it in SQL Server

Question

My sql is very simple. Version: SQL Server 2008

UPDATE dbo.abc SET UrlName = 'L££££' WHERE abcID = 10;

select UrlName from abc where abcID = 10;

What I get is

"LLLLL"

I queried the SELECT @@language, it is "British". So dont think it is about culture settings.

Any thoughts?

Answer 1

£ is not one of the characters supported in that code page.

To see them all you can use

DECLARE @Collations TABLE
(
     code TINYINT PRIMARY KEY,
     Slovenian_CI_AS CHAR(1) COLLATE Slovenian_CI_AS 
);

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),   --2
        E02(N) AS (SELECT 1 FROM E00 a, E00 b), --4
        E04(N) AS (SELECT 1 FROM E02 a, E02 b), --16
        E08(N) AS (SELECT 1 FROM E04 a, E04 b) --256
INSERT INTO @Collations
            (code)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1
FROM   E08

UPDATE @Collations
SET    Slovenian_CI_AS = CAST(code AS BINARY(1))

SELECT *
FROM   @Collations 

You will need to change the column collation or use nvarchar rather than varchar.

Which ever option you choose if your database collation remains at Slovenian_CI_AS you will still need to add an N prefix to the string literal to avoid it being coerced into that code page first.

UPDATE dbo.abc
SET    UrlName = N'L££££'
WHERE  abcID = 10;