collation problem with spanish character

Question

Post reply

collation problem with spanish character

freewarefw

Right there with Babe

Points: 715
More actions
February 6, 2007 at 1:21 am

#172121

Hi all,
we have applications for SQL2000. we have problem with spanish characters.
how can we explain the script below :
select ASCII( 'Ñ'), ASCII( 'Ð'), ASCII( '¥')
----------- ----------- -----------
165         209         190
select UNICODE('Ñ'), UNICODE('Ð'), UNICODE('¥')
----------- ----------- -----------
209         208         165
our database is configured as this : Collation name : SQL_Latin1_General_CP850_BIN
In isqlw, tools\connections, Even the parameter 'Perform translation for character data' is ticked or not, I have the same result.
An other exemple : setup with 'Perform translation for character data' not ticked
set nocount on
-- drop table t1
-- create table t1 (txt char(5), nbr tinyint )

declare @txt   char(5)
declare @ntxt nchar(5)

delete t1

select @txt = 'nñn', @ntxt = 'nñn'
select @txt,1, @ntxt,2
insert into t1 values (@txt, 1)
insert into t1 values (@ntxt, 2)

select @txt = 'NÑN', @ntxt = 'NÑN'
select @txt,3, @ntxt,4
insert into t1 values (@txt, 3)
insert into t1 values (@ntxt, 4)

select txt CHAR, cast(txt as nchar(5)) NCHAR, nbr from t1

here the result :
CHAR NCHAR nbr
----- ----- ----
n¤n   nñn   1
n¤n   nñn   2
N¥N   NÑN   3
N¥N   NÑN   4
Some help will be appreciated.
Thanks for your reply.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you change your collation as seen below, you should see the results you expect:

CREATE TABLE [t1] (

[txt] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[nbr] [tinyint] NULL

) ON [PRIMARY]

GO

when you run the same work you posted, all fields appear as expected.

below is a some sqls that will help you change the collation of an entire database, and all fields in the database as well.

it's limited to top 5 for demo purposes, so change it when you need the sqls for production:

typical results:

alter database TextManip COLLATE SQL_Latin1_General_CP1_CI_AS

ALTER TABLE t1 ALTER COLUMN txt char(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

ALTER TABLE WafflePhrases ALTER COLUMN Phrase varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

ALTER TABLE WafflePhrases ALTER COLUMN PhraseType varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

SELECT ' alter database ' + db_name() + ' COLLATE SQL_Latin1_General_CP1_CI_AS'

SELECT TOP 5

'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '

+ TYPE_NAME(SYSCOLUMNS.XTYPE) + '(' + CONVERT(VARCHAR,SYSCOLUMNS.LENGTH) + ') '

+ ' COLLATE SQL_Latin1_General_CP1_CI_AS'

+ CASE ISNULLABLE WHEN 0 THEN ' NOT NULL' WHEN 1 THEN ' NULL' END AS ALTERSTMT,

SYSOBJECTS.NAME AS TBLNAME,

SYSCOLUMNS.NAME AS COLNAME,

TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE,

SYSCOLUMNS.LENGTH as length

FROM SYSOBJECTS

INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID

WHERE SYSOBJECTS.XTYPE='U'

AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('char','varchar', 'nchar','nvarchar')

ORDER BY TBLNAME,COLNAME

SELECT TOP 5

'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '

+ TYPE_NAME(SYSCOLUMNS.XTYPE)

+ ' COLLATE SQL_Latin1_General_CP1_CI_AS'

+ CASE ISNULLABLE WHEN 0 THEN ' NOT NULL' WHEN 1 THEN ' NULL' END AS ALTERSTMT,

SYSOBJECTS.NAME AS TBLNAME,

SYSCOLUMNS.NAME AS COLNAME,

TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE,

SYSCOLUMNS.LENGTH as length

FROM SYSOBJECTS

INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID

WHERE SYSOBJECTS.XTYPE='U'

AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('text','ntext')

ORDER BY TBLNAME,COLNAME

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gift Peddie SSC Guru Points: 73570 More actions · Answer 2

SQL_Latin1_General_CP850_BIN

It is actually simple you have a database that is BIN(binary sort) the fastest sort but it also require your data to be case sensitive by default which includes stored procs so try the link below for the correct code page 850 you need. If you can't change the collaton then run a search for collation precedence in the BOL(books online). Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms144250.aspx

Kind regards,
Gift Peddie

freewarefw Right there with Babe Points: 715 More actions · Answer 3

thanks for your answers.

We thought to change de field into our table by adding the right Collation but we need to be Case Sensitive as we use Uppercase and Lowercase characters in our application.

Also, our product could be used by others peoples like Danish, FInnish, Spanish and so all, therefore specific characters.

???

thanks

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

I don't think you should run Danish and Finish languages with a database created for Spanish language because of the differences, the former are similar but cannot be compared to the later. Now to case sensitive data that is what string functions are for, in the FCL(framework class library) it is very simple String.ToLower or String.ToUpper. It is not complicated create a test database and play with the collations of all languages you plan to support and select the correct one, then you will see case sensitivity is not as important as you think. Hope this helps.

Kind regards,
Gift Peddie