Specifying the USING CHAR_CS argument converts text into the database character set. The output datatype is VARCHAR2. Specifying the USING NCHAR_CS argument converts text into the national character set. The output datatype is NVARCHAR2. Example 1 CREATE TABLE t1 (char_col CHAR(20), nchar_col nchar(20)); INSERT INTO t1 VALUES ('Hi', N'Bye'); SELECT * FROM t1;CHAR_COL NCHAR_COL -------- --------- Hi Bye Example 2 UPDATE t1 SET nchar_col = TRANSLATE(char_col USING NCHAR_CS); UPDATE t1 SET char_col = TRANSLATE(nchar_col USING CHAR_CS); SELECT * FROM t1;CHAR_COL NCHAR_COL -------- --------- Hi Hi Example 3 UPDATE t1 SET nchar_col = TRANSLATE('deo' USING NCHAR_CS); UPDATE t1 SET char_col = TRANSLATE(N'deo' USING CHAR_CS); SELECT * FROM t1;CHAR_COL NCHAR_COL -------- --------- deo deo
非常感谢bzszp,但是UPDATE t1 SET nchar_col = TRANSLATE(char_col USING NCHAR_CS);这样作nvarchar的值就变了。UPDATE t1 SET char_col = TRANSLATE(N'deo' USING CHAR_CS);这样做是将nvarchar转为了varchar但前提是表中必须有varchar类型的字段。请指点!
表不多的话可以这样//创建一个新表,并把数据转换为varchar2 create table newtbname as select col1,translate(col2 using char_cs),col3 from tbname; rename tbname to tbname_temp;//将原先的表重新命名 rename newtbname to tbname;//把新创建的表改名为原先的表名 这样就转换过来了 作完后,把tbname drop掉,rename tbname_temp to tbname就行了这样应该可以解决问题
Example 1
CREATE TABLE t1 (char_col CHAR(20),
nchar_col nchar(20));
INSERT INTO t1
VALUES ('Hi', N'Bye');
SELECT * FROM t1;CHAR_COL NCHAR_COL
-------- ---------
Hi Bye
Example 2
UPDATE t1 SET
nchar_col = TRANSLATE(char_col USING NCHAR_CS);
UPDATE t1 SET
char_col = TRANSLATE(nchar_col USING CHAR_CS);
SELECT * FROM t1;CHAR_COL NCHAR_COL
-------- ---------
Hi Hi
Example 3
UPDATE t1 SET
nchar_col = TRANSLATE('deo' USING NCHAR_CS);
UPDATE t1 SET
char_col = TRANSLATE(N'deo' USING CHAR_CS);
SELECT * FROM t1;CHAR_COL NCHAR_COL
-------- ---------
deo deo
create table newtbname as select col1,translate(col2 using char_cs),col3 from tbname;
rename tbname to tbname_temp;//将原先的表重新命名
rename newtbname to tbname;//把新创建的表改名为原先的表名
这样就转换过来了
作完后,把tbname drop掉,rename tbname_temp to tbname就行了这样应该可以解决问题