Today’s Tips – Use Thailand Character, Database Collation

Here I am again, with the problem : how to use non-Latin characters in your Application ?

at the Programming side, you need configure the ColdFusion so it can support the non-Latin characters, please see my previous post that explain how to do it, but now, we’ll go with the database.

I assume you usually creates an application in EN localization, that’s why, by default, all the setting will refer to EN. included your SQL database collation, which by default, it can not merely accept the Thailand’s characters since the Collation is Latin. the solution is : you need to update the Database and Table Column’s Collation.

Collation for EN localization default is SQL_Latin1_General_CP1_CI_AS (at least this default collation where my Computer Server is installed)

in Thai, you need to use THAI_CI_AS collation (at least this what I used by now, probably you can use THAI_CI or other THAI collation).

1. Update Database Collation. to Alter the database collation, you can go to SQL Management, under the Object Browser, find the database you want to alter. then right-click to edit the database properties. you will find Collation options in the pop up screen, then you need to select the THAI_CI_AS for the database collation.

please note that you can update the collation if Database is not being used, if it is, then you can Restart the SQL server and try again.

2. Update Table’s Column Collation. You can run this SQL script to alter the column’s collation. (just example, you can modify the table name and column name) :

 ALTER TABLE temployee ALTER COLUMN first_name varchar(50) COLLATE THAI_CI_AS  

Then now, after you update the database and table’s column collation, you Now should be able to put Thai character to the database. This solution can be implemented in Database which has only less number of tables, but how if you has hundreds of Tables ? please find the tips on my next Post :)

Until next time, keep cheer up !