Oracle nvarchar2錯誤

Oracle nvarchar2 錯誤

 

問題：

使用substr函式擷取指定字串時，取出4位字元 年(例如2019),結果只取出3位(例如201)；

 

問題原因：

字元型別 nvarchar2

 

解決方案：

透過TRANSLATE函式將 nvarchar2 轉換成 varchar2 。

 

問題重現：

---1 建立測試資料

create table t1(id number,t_format nvarchar2(100),t_name varchar2(100));

insert into t1 values(1,'abcde2019xxx0707uuu','abcde2019xxx0707uuu');

commit;

---2 透過substr函式擷取年月日

select id,

       t_format,

       t_name,

       length(t_format),

       length(t_name),

       substr(t_format, 6, 4),

       substr(t_format, 13, 4),

       substr(t_name, 6, 4),

       substr(t_name, 13, 4)

  from t1;

select id,

       t_format,

       t_name,

       substr(t_format, 6, 5),

       substr(t_format, 13, 5),

       substr(t_name, 6, 4),

       substr(t_name, 13, 4)

  from t1;

 

---3 透過TRANSLATE函式將 nvarchar2 轉換成 varchar2

select id,

       t_format,

       t_name,

       substr(utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(Translate(t_format USING CHAR_CS))),6,4),

       substr(utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(Translate(t_format USING CHAR_CS))),13,4),

       substr(t_name, 6, 4),

       substr(t_name, 13, 4)

  from t1;

NVARCHAR2 Data Type

The  NVARCHAR2  data type is a Unicode-only data type. When you create a table with an  NVARCHAR2  column, you supply the maximum number of characters it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the maximum length of the column.

The maximum length of the column is determined by the national character set definition. Width specifications of character data type  NVARCHAR2  refer to the number of characters. The maximum column size allowed is 4000 bytes.

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！