How to read the length of a string in Oracle?

Question

I created the following table with one text column:

create table table1 (
  col1 varchar2(20)
);

Then I inserted one Polish letter to it:

insert into table1 values ('ą')

How can I obtain the number of characters in this string? It should be 1, but all functions that I know return other results: LENGTH(COL1) -> 2, LENGTHC(COL1) -> 2, LENGTH2(COL1) -> 2, LENGTH4(COL1) -> 2, LENGTHB(COL1) -> 6.

The SELECT * FROM NLS_DATABASE_PARAMETERS; returns:

PARAMETER              VALUE
---------------------------------
NLS_RDBMS_VERSION      18.0.0.0.0
NLS_NCHAR_CONV_EXCP    FALSE
NLS_LENGTH_SEMANTICS   BYTE
NLS_COMP               BINARY
NLS_SORT               BINARY
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET       AL32UTF8
NLS_ISO_CURRENCY       AMERICA
NLS_TERRITORY          AMERICA
NLS_LANGUAGE           AMERICAN
...                    ...

You can check it on: dbfiddle.uk.

select dump(col1) from table1 shows that the character is stored in six bytes. I can't reproduce that either on my own computer or on livesql; that's a question for dbfiddle. Did you try it on your own computer, or only on dbfiddle? — user5683823
– user5683823, Commented Nov 13, 2020 at 14:27
Seems like an Oracle 18.4 bug (which dbfiddle is using). It works for me on 12.1, 12.2 18.7, 18.10 and 19.8 — user330315
– user330315, Commented Nov 13, 2020 at 14:28
I was testing it only on dbfiddle. Thank you - this solves the problem. — iwis
– iwis, Commented Nov 13, 2020 at 14:29
@a_horse_with_no_name - to me it seems more likely that it's a dbfiddle bug (perhaps in how it reads characters from its web interface, or whatnot) rather than an Oracle bug, regardless of version. — user5683823
– user5683823, Commented Nov 13, 2020 at 14:30

EJ Egyed · Accepted Answer · 2020-11-13 15:25:15Z

2

It is most likely a bug with the version of Oracle you are running the query on or an issue with the character being encoded on the web server.

I have almost all of the same NLS parameters on my system except I am on Oracle 19 instead of 18.

Even on livesql.oracle.com it returns the results as you would expect but again, that database is on Oracle 19.

edited Nov 13, 2020 at 15:25

answered Nov 13, 2020 at 14:04

EJ Egyed

6,1341 gold badge11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iwis Over a year ago

Thanks. Now I am testing SQL queries on livesql.oracle.com and they works properly.

Collectives™ on Stack Overflow

How to read the length of a string in Oracle?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related