Clever Geek Handbook

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

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;

I get

"LLLLL"

I requested the SELECT @@ language, which is "British". So don't think this is about culture settings.

Any thoughts?

+3


source to share


1 answer


£

is not one of the characters supported in this 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 collation of the columns, or use nvarchar

instead varchar

.

Whichever option you choose, if the database collation stays in Slovenian_CI_AS

, you still need to prefix N

the string literal to avoid being forced into that code page.

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


source






More articles:


All Articles