C-cedilla -- Special Character

**NeoPa** · Nov 29 '12, 02:32 AM

Only the first 128 characters can be assumed to be standard in ASCII across different character sets. 199 is C-cedilla in one at least, but I look at others and find it's something else entirely. You probably need to look at what is interpreting the character exported and which CharSet it's using.

**Rabbit** · Nov 29 '12, 02:58 AM

U+00C7 is unicode, hence the U. If you want to use unicode encoding, then you need to use NCHAR. The rest of your data will have to be converted to NCHAR and NVARCHAR if they are not.

If you want to stay with ASCII, then capital C cedilla is 128 and lower case c cedilla is 135.

If you need to use any of the other cedillas, you will have to switch to unicode.

**ddtpmyra** · Nov 29 '12, 03:29 AM

@NeoPa:
It interprets it as a '€' char.

@Rabbit:
Can you give me an example of a select that will use U+00C7?

**ddtpmyra** · Nov 29 '12, 03:34 AM

@NeoPa:
When I select the temp table inside SQL it is in Cedill form but everytime I export it to a .dat file it's different €.

I wonder if I'm using the correct BCP format (below) any other suggestions?

Code:

SELECT @SQL='BCP testfile OUT '+@FILENAME+' -c -t -T -r '+@@SERVERNAME

**NeoPa** · Nov 29 '12, 03:49 AM

The contents of the file are not characters as such. The numbers are interpreted as characters by whatever software is used to view them. What character sets it uses depends on how your system is set up.

It may well be that Rabbit's suggestion of using unicode variables (NCHAR & NVARCHAR) to hold your data is the way to go. Have you looked into that yet?

**ddtpmyra** · Nov 29 '12, 03:54 AM

Originally posted by NeoPa

The contents of the file are not characters as such. The numbers are interpreted as characters by whatever software is used to view them. What character sets it uses depends on how your system is set up.

It may well be that Rabbit's suggestion of using unicode variables (NCHAR & NVARCHAR) to hold your data is the way to go. Have you looked into that yet?

Sorry I dont know how to do that. Can you show me how like an example?

thank you.

**Rabbit** · Nov 29 '12, 06:38 AM

To create a unicode string, you use the NCHAR function.

Code:

SELECT NCHAR(199)

But here's the thing, just because you write the data in unicode, unless you're also reading it in unicode, it won't look right. What you really need to do is find out the encoding that the reader will be using and then write to match that.

**NeoPa** · Nov 29 '12, 01:46 PM

Rabbit's illustration shows how to cast a value into Unicode format - NCHAR(). This would still need to be stored, if it were stored, in a variable that is also defined as NCHAR.

That would indicate that your original line of code would be :

Code:

DECLARE @TAB NCHAR(1)=NCHAR(199)

I'm not sure what the Unicode equivalent of the C-cedilla is though. Probably not 199, but I'm sure you can find that.

PS. Scratch that. It is 199 (or &hC7) for the capital and 131 (or &hE7) for the lower-case. See W3 - Characters Ordered by Unicode.

**Rabbit** · Nov 29 '12, 04:23 PM

Yes it is, at least for UCS-2 (which is what SQL Server uses for NCHAR) and also UTF-16, and the equivalent in ASCII for capital C cedilla is 128 and lower case c cedilla is 135. UCS-2 and UTF-16 also use 2-bytes per character whereas ASCII uses only one.

**ddtpmyra** · Nov 29 '12, 05:48 PM

i can see the cedill in right format when stored on temporary table this is good! But every time I export it to a .dat file it looks like this...

€

I wonder if the cause of these is because how i called it on my BCP

Code:

SELECT @SQL='BCP Test_File OUT '+@FILENAME+' -c -t -T -S '+@@SERVERNAME

**Rabbit** · Nov 29 '12, 06:29 PM

You have two options.

1) Use -w instead of -c. -w will encode the text in unicode.
2) Use -c but also specify -C ACP to use code page 1252. Code page 1252, also known as Latin 1, is the most common code page used on windows.

**ddtpmyra** · Nov 29 '12, 08:06 PM

You are awesome NeoPa and Rabbit! It works thank you soooo much for your help!

**NeoPa** · Nov 29 '12, 08:14 PM

I'm glad we could help, though TBF I think Rabbit's know-how was probably more helpful than mine for this one. You may want to decide which of the posts helped you nail it in the end and select it as Best Answer. I'd do it for you, but I can't tell where you were most stuck and which one opened the gates for you.

C-cedilla -- Special Character

C-cedilla -- Special Character

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment