Converting text data to ntext data
-
Hi all,
My organization have a web-based application and needs it to support multilingual so we will be adapting our app to use unicode. However, one of our problems is to convert existing data from text to ntext. I couldn't find anything that document this. What is the best way to do that? I would like to be able to migrate the data from an existing text column to another ntext column in the table.
I brief you about my system, I used List manager system to store the messages and distribute to all members. Right now,by design the Lyris system keep the message in the text field which mean it 's not support multilanguage directly because of unicode field. We needs to create new Db which has the data structure as same as Lyris but just one difference is keep the message in unicode format (ntext) which we need the sql script to automatically update the new record get from Lyris to new DB.
If I can't do that, what are the options? If it's possible, I would like to be able to do this in sql script.
Thanks a mil, in advance.
Eddie
-
create table t1 (coltxt text)
go
-- put some data in it
insert into t1 ( coltxt ) values ('some test data')
go
-- add unicode column
alter table add colUnicode ntext
go
-- transfer values
update t1 set ColUnicode = coltxt
go
-- get rid of old column
alter table t1
drop column coltxt
go
-- rename new with the name of the old column
sp_rename 't1.ColUnicode', 'coltxt', 'COLUMN'
go
-- tell sql storage engine about the text columns allocation changes
dbcc checktable ( 'yourDBName', 't1')
go
-- force recompilation. This maybe optional
dbcc freeproccache
go
Cheers!!
* Noel -
Hi Noel,
Thank you for your reply,
I have found the solution which I must be read the data in the 1252 codepage(because of SQL Latin collation) and display in a utf-8 codepage which it doesn't metter the we store in either text or ntext.
Eddie
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply