What sorting should I use to store £ (British pound sign) in a cook?

Question

I am using SQL Server Express 2008 R2.

I am importing from a csv file, and some of the columns have an “E” as part of some free text. When this file is uploaded to the database, the “£” sign is displayed as “ú”. I think this is definitely related to database sorting. The current database mapping is Latin1_General_CI_AS.

That sorting will save "£" as "£" in SQL Server.

Thank you very much.

Additional information: I created a small file to demonstrate my problem here: https://www.dropbox.com/s/yvcx4t9nk9p0bf7/poundTest.txt

use myDB; go create table test (id int, amt_range varchar(50)); bulk insert test from 'F:\poundtest.txt' with ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', firstrow=1 ); select * from test; 

This returns:

 id amt_range 1 <-ú200K 2 -ú200K to -ú20k 3 -ú20k to ú0k 4 ú0k to ú20k 5 ú20k to ú200k 6 >ú200k 

Answer 1

SQL Server will definitely store "£" correctly in a varchar or nvarchar column using the Latin1_General_CI_AS . I see this happening every day in the software that I support.

I think the problem is that the text file is encoded and read. "£" has a code point value of 163 on Windows-1252 and Unicode. However, in extended ASCII (for example, DOS code on page 850), the value “£” is 156, and “ú” is 163. Is your code an attempt to convert csv text encoding before transferring data to SQL Server? If csv is encoded as UTF-8, then conversion from ASCII is not required.

UPDATE

Looking at MSDN, it seems like the bulk insert command is performing character set conversion. OEM is the default option if not specified.

CODEPAGE = {'ACP' | "OEM" | 'RAW' | 'code_page'}

The default is definitely not what you want here. Ideally, you should specify UTF-8 ( CODEPAGE = '65001' ). However, MSDN says that UTF-8 is not supported.

I suggest you change the encoding of your CSV file to Windows-1252, and then use the CODEPAGE = 'ACP' parameter to import the data.