We tried that, altering the column from 10 to 15 characters. Our column can now correctly store the full 10 characters of a postal code string.<\/p>\n
But then, another problem showed up when we needed to match on the first 5 characters in the ZIP code.<\/p>\n
\n
create<\/span> table<\/span> Test2 ( V1 varchar2(15), N1 nvarchar2(15) ) ;
insert into<\/span> Test2 values<\/span> ( '1234567'<\/span>, '1234567'<\/span> );
insert into<\/span> Test2 values<\/span> ( '123456'<\/span>, '123456'<\/span> );
insert into<\/span> Test2 values<\/span> ( '12345'<\/span>, '12345'<\/span> );
insert into<\/span> Test2 values<\/span> ( '1234'<\/span>, '1234'<\/span> );
insert into<\/span> Test2 values<\/span> ( '123'<\/span>, '123'<\/span> );
commit<\/span>;
SQL<\/span>> select<\/span> V1, substr( V1, 1,5 ), N1, substr( N1, 1, 5 ) from<\/span> Test2;
V1 SUBSTR(V1,1,5) N1 SUBSTR(N1,1,5)
--------------- -------------- ------------------------------ --------------<\/span>
1234567 12345 1234567 1234
123456 12345 123456 1234
12345 12345 12345 1234
1234 1234 1234 1234
123 123 123 123
<\/pre>\n<\/div>\n
Yup, that’s right. SUBSTR( NVATCHAR2 ) does not return the full 5 characters! <\/p>\n
As a workaround, we can use SUBSTRC():<\/p>\n
\n
SQL<\/span>> select<\/span> V1, substrc( V1, 1,5 ), N1, substrc( N1, 1, 5 ) from<\/span> Test2;
V1 SUBSTRC(V1,1,5) N1 SUBSTRC(N1,1,5)
--------------- --------------- ------------------------------ ------------------------------<\/span>
1234567 12345 1234567 12345
123456 12345 123456 12345
12345 12345 12345 12345
1234 1234 1234 1234
123 123 123 123<\/pre>\n<\/div>\n
Now the values retrieved from the NVARCHAR2 column are working as expected.<\/p>\n
Bottom Line:<\/h3>\n\n- Don’t use NVARCHAR2 for size-limited columns, unless you absolutely have to<\/li>\n
- If you have to use NVARCHAR2, us SUBSTRC() for partials.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
Consider the following POSTAL_CODE column: create table Test1 ( POSTAL_CODE NVARCHAR2(10)); insert into Test1 values (‘12345-1234′);commit; select * from Test1; POSTAL_CODE——————–12345-123 That’s right – the NVARCHAR2(10) column appears to lop of 10th character and doesn’t store it. This behavior must be related to multi-byte character, and Unicode, somehow. The Oracle DB docs on Globalization Support […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-36","post","type-post","status-publish","format-standard","hentry","category-oracle","post-preview"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts\/36","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/comments?post=36"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts\/36\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/media?parent=36"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/categories?post=36"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/tags?post=36"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}