column collation - the mystery of it all ?

Question

column collation - the mystery of it all ?

szejiekoh

SSCarpal Tunnel

Points: 4155
More actions
March 19, 2017 at 8:34 am

#336154

Hi all,
I am trying out to support/insert a different language/character other then the default codepage in the current database which is SQL_Latin1_General_CP1_CI_AS. Thus i am trying to use column collation to support another characterset.
This is what I have tested.
Instance/Database Default CS -> SQL_Latin1_General_CP1_CI_AS
create table test(a varchar(20), b varchar(20) collate Thai_CS_AI);
insert into test values ('a','b'); insert into test values ('a','ไกไไกกง');
select * From test;
a b a ???????
============
If I created another database with the default collation as "Thai_CS_AI" , i have absolutely no problem displaying the result correct.
-- create database thaidb with default collation as "Thai_CS_AI"
create table test(a varchar(20), b varchar(20) ); -- did not set column collation anymore since DB default is already set so
insert into test values ('a','b'); insert into test values ('a','ไกไไกกง');
select * From test; a b a ไกไไกกง
I am using SQL management studio for the above testing.
Why isn't my column collation working =(
Please help~
Regards,
Noob

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 1

I believe that you need to make it so that the insert is of the collation you desire, as well.
create table test(a varchar(20), b varchar(20) collate Thai_CS_AI);

insert into test values ('a','b'); insert into test values ('a','???????' collate Thai_CS_AI);

select * From test;

The reason you don't need to do that if you make the whole server of that collation is because that affects everything including the inserts.

Not sure, but you may have to use the collation on the column when you do a select from it, as well but haven't tried it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

szejiekoh SSCarpal Tunnel Points: 4155 More actions · Answer 2

Jeff Moden - Sunday, March 19, 2017 1:53 PM
I believe that you need to make it so that the insert is of the collation you desire, as well.
create table test(a varchar(20), b varchar(20) collate Thai_CS_AI); insert into test values ('a','b'); insert into test values ('a','à¹„à¸à¹„à¹„à¸à¸à¸‡' collate Thai_CS_AI);
select * From test;
The reason you don't need to do that if you make the whole server of that collation is because that affects everything including the inserts.
Not sure, but you may have to use the collation on the column when you do a select from it, as well but haven't tried it.

Hi Jeff,
Thanks, sorry for the late reply.
Didn't know that we can do a collate on insert as well but well that didn't work also.

Can you let me know how do we specify a collate on SELECT ?

Tried SELECT b collate Thai_CS_AI from tab;
Still failed =(

Regards,
Noob

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 3

szejiekoh - Wednesday, March 22, 2017 10:30 AM
Jeff Moden - Sunday, March 19, 2017 1:53 PM
I believe that you need to make it so that the insert is of the collation you desire, as well.
create table test(a varchar(20), b varchar(20) collate Thai_CS_AI); insert into test values ('a','b'); insert into test values ('a','à¹„à¸à¹„à¹„à¸à¸à¸‡' collate Thai_CS_AI); select * From test;
The reason you don't need to do that if you make the whole server of that collation is because that affects everything including the inserts.
Not sure, but you may have to use the collation on the column when you do a select from it, as well but haven't tried it.
Hi Jeff,
Thanks, sorry for the late reply.
Didn't know that we can do a collate on insert as well but well that didn't work also.
Can you let me know how do we specify a collate on SELECT ?
Tried SELECT b collate Thai_CS_AI from tab;
Still failed =(
Regards,
Noob

Ah... crud... my bad and my apologies. See the comments in the following. You sometimes have to give it the "Unicode nudge".

[

create table test(a varchar(20), b varchar(20) collate Thai_CS_AI);

insert into test values ('a','b'); insert into test values ('a','à¹„à¸à¹„à¹„à¸à¸à¸‡' collate Thai_CS_AI); --Even this won't work insert into test values ('a',N'à¹„à¸à¹„à¹„à¸à¸à¸‡' ); --But this does

select * From test;

Results:
a b -------------------- -------------------- a b a ??????? a à¹„à¸à¹„à¹„à¸à¸à¸‡

(3 row(s) affected)

I left the column definitions in the table as they were but you might want to change the "b" column to NVARCHAR just so folks "get it".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

szejiekoh SSCarpal Tunnel Points: 4155 More actions · Answer 4

Jeff Moden - Wednesday, March 22, 2017 10:34 PM
szejiekoh - Wednesday, March 22, 2017 10:30 AM
Jeff Moden - Sunday, March 19, 2017 1:53 PM
I believe that you need to make it so that the insert is of the collation you desire, as well.
create table test(a varchar(20), b varchar(20) collate Thai_CS_AI); insert into test values ('a','b'); insert into test values ('a','à¹„à¸à¹„à¹„à¸à¸à¸‡' collate Thai_CS_AI); select * From test;
The reason you don't need to do that if you make the whole server of that collation is because that affects everything including the inserts.
Not sure, but you may have to use the collation on the column when you do a select from it, as well but haven't tried it.
Hi Jeff,
Thanks, sorry for the late reply.
Didn't know that we can do a collate on insert as well but well that didn't work also.
Can you let me know how do we specify a collate on SELECT ?
Tried SELECT b collate Thai_CS_AI from tab;
Still failed =(
Regards,
Noob
Ah... crud... my bad and my apologies. See the comments in the following. You sometimes have to give it the "Unicode nudge".
[
create table test(a varchar(20), b varchar(20) collate Thai_CS_AI);
insert into test values ('a','b'); insert into test values ('a','à¹„à¸à¹„à¹„à¸à¸à¸‡' collate Thai_CS_AI); --Even this won't work insert into test values ('a',N'à¹„à¸à¹„à¹„à¸à¸à¸‡' ); --But this does
select * From test;

Results:
a b -------------------- -------------------- a b a ??????? a à¹„à¸à¹„à¹„à¸à¸à¸‡
(3 row(s) affected)
I left the column definitions in the table as they were but you might want to change the "b" column to NVARCHAR just so folks "get it".

Hi Jeff,
Yeap the specification of 'N' did help. But i thought 'N' = unicode. But i can't wrap my head around on how does a "Unicode" encoding be able to insert into a "Thai" encoding column. The thai words have the same code point in both Unicode and Thai encoding ?

Regards,
Noob

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 5

To be honest, I don't know the answer to your latest question. I have a difficult enough time even getting people to use CR/LF symbols consistently for imports.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

szejiekoh SSCarpal Tunnel Points: 4155 More actions · Answer 6

Jeff Moden - Thursday, March 23, 2017 1:26 PM
To be honest, I don't know the answer to your latest question. I have a difficult enough time even getting people to use CR/LF symbols consistently for imports.

Hi Jeff,

I am so sorry for the late reply. Your honest reply is more then enough assurance for me.

Thank you!