Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL Server with UTF Support

Author  Topic 

imdadmca
Starting Member

9 Posts

Posted - 2007-11-27 : 00:51:31
Hi,

I've created a new database, after creation i want to give utf-8 support for that database.

# Is there any particular collation available to support utf-8 support.

# Or is there anynway to make it is possible???????????

Thanks in Advance
Imdad

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-27 : 01:44:25
You can use unicode data type (nvarchar, nchar, ntext).
Go to Top of Page

imdadmca
Starting Member

9 Posts

Posted - 2007-11-27 : 01:52:48
Is there any other possible way. because i am not going create tables and all those stuffs!. because all those stuffs done by another side.

In oracle, while Db creation itself there s an option to choose the UTF support like tht is thers any thing in sql server studio????????????

Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-27 : 01:57:23
You can choose collation when create db, table even column.
Go to Top of Page

imdadmca
Starting Member

9 Posts

Posted - 2007-11-27 : 02:02:41
Normally we select SQL_Latin........!!
but it wont support non-ASCII datas.

is there any collation type to support UTF chars??
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-27 : 02:45:18
Take look at 'Managing Data Conversion Between Unicode Encoding Schemes' in books online.
Go to Top of Page

imdadmca
Starting Member

9 Posts

Posted - 2007-11-27 : 03:53:03
I tried lot on net.
i couldn't get a exact info!!!!!!!
Could u plz suggest any URL
Go to Top of Page

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-27 : 04:17:31
No matter what is a collation of a server and a database, any column of a table can have a different collation - server and database collations are just a default.

What you wrote:
---------------
Normally we select SQL_Latin........!!
but it wont support non-ASCII datas.
---------------
is absolutely wrong, with SQL_Latin collation you can store Unicode characters, you just need to create a column of a type n(var)char or ntext

Also, if
>because i am not going create tables and all those stuffs!. because all those stuffs done by another side
if you do not create tables, you have no control how data is stored. *THE ONLY WAY* is to ask "another side" to create n(var)char columns.

And finally, SQL server stores unicode characters using 2 bytes per character. It is not using UTF-8 internally.
But in any case, when your application forms a SQL statement:

insert into MyUnicodeTab (txt) values( N'MyUnicodetext')

it performs a conversion UTF-8 -> 2 bytes per char before quering SQL
When SQL returns unicode data, it also returns 2 bytes per char, as it works that way in all applications.
Go to Top of Page

imdadmca
Starting Member

9 Posts

Posted - 2007-11-27 : 04:44:24
Thanx for ur comments!(evilDBA)

I wil give a real time hurdle i have OK

" I want to store/retrieve Japanese chars without changing the table definition into like nchar,nvarchar and ntext"

Go to Top of Page

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-27 : 05:31:09
>" I want to store/retrieve Japanese chars without changing the table definition into like nchar,nvarchar and ntext"
It is not possible unless you encode/decode these chars into ASCII chars manually in your application.
Go to Top of Page

imdadmca
Starting Member

9 Posts

Posted - 2007-11-27 : 05:53:55
how can i encode without changing the table def.
Go to Top of Page

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-27 : 06:09:42
The general answer to your question is NO, you can not store Unicode data in ascii columns.

What I suggested is just a workaround, so dirty, that I dont recommend to use it.
But theoretically you can replace chars with {0xHexCode}
Your strings will look very ugly, LIKE and other operation won't work, but yes, you will be able to store some Unicode data in non-unicode columns :)

In any case, the only real way to store unicode data in ascii columns is to change column type.
In many cases it van be done by a single SQL command, and many application even won't notice that they are working with nvarchar, not varchar.
Go to Top of Page

imdadmca
Starting Member

9 Posts

Posted - 2007-11-27 : 06:11:38
What is SQL command to change the data type?
Go to Top of Page

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-27 : 07:07:50
ALTER TABLE tablename ALTER COLUMN columnname newtype [(len]) [null]

Note: if column is indexed, you need to drop idnex first, change column type and then recreate an index.
Go to Top of Page

imdadmca
Starting Member

9 Posts

Posted - 2007-11-28 : 04:46:25
Is it related to windows collation name??

Thanx in Advance
Go to Top of Page

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-28 : 06:45:02
No, SQL server collations can be differet from the default Windows collation
Go to Top of Page

imdadmca
Starting Member

9 Posts

Posted - 2007-11-28 : 07:20:03
Then what is the page conversion code??

Thanx in Advance
Go to Top of Page

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-28 : 07:40:34
I dont understand your question.
For non-unicode data (varchar) collation defines how characters with codes >=128 are interpreted, and how data is compared and sorted.
For unicode data (nvarchar), collation defines only comparison and sorting, but of course, any character of any language can be stored without any data loss in any unicode column, no matter what a collation is.
You can store japan characters in nvarchar with latin collation.
Windows default collation for non-unicode proigram defines how ascii 128..255 are mapped into unicode data for old programs, used 1 byte per char.
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-29 : 07:15:35
I think you could gain a LOT of benefit from reading every link in my signature....

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page
   

- Advertisement -