共计 1606 个字符，预计需要花费 5 分钟才能阅读完成。
问题：
使用 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;
https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#SQLRF30020
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.
更多 Oracle 相关信息见 Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12
：
