pink cylinders

More than Four

...there's an axis for that.

More pitfalls with NVARCHAR2 vs VARCHAR2

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 can get quite detailed, but I still haven't found a concise explanation of this silent truncation behavior.

So, just make the column larger, right?

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.

But then, another problem showed up when we needed to match on the first 5 characters in the ZIP code.

Consider:

create table Test2 ( V1 varchar2(15), N1 nvarchar2(15) ) ;
insert into Test2 values ( '1234567', '1234567' );
insert into Test2 values ( '123456', '123456' );
insert into Test2 values ( '12345', '12345' );
insert into Test2 values ( '1234', '1234' );
insert into Test2 values ( '123', '123' );
commit;

SQL> select V1, substr( V1, 1,5 ), N1, substr( N1, 1, 5 ) from Test2;

V1 SUBSTR(V1,1,5) N1 SUBSTR(N1,1,5)
--------------- -------------- ------------------------------ --------------
1234567 12345 1234567 1234
123456 12345 123456 1234
12345 12345 12345 1234
1234 1234 1234 1234
123 123 123 123

Yup, that's right. SUBSTR( NVATCHAR2 ) does not return the full 5 characters!

As a workaround, we can use SUBSTRC():

SQL> select V1, substrc( V1, 1,5 ), N1, substrc( N1, 1, 5 ) from Test2;

V1 SUBSTRC(V1,1,5) N1 SUBSTRC(N1,1,5)
--------------- --------------- ------------------------------ ------------------------------
1234567 12345 1234567 12345
123456 12345 123456 12345
12345 12345 12345 12345
1234 1234 1234 1234
123 123 123 123

Now the values retrieved from the NVARCHAR2 column are working as expected.

Bottom Line:

  • Don't use NVARCHAR2 for size-limited columns, unless you absolutely have to
  • If you have to use NVARCHAR2, us SUBSTRC() for partials.

Add comment

Loading