Question: Can
somebody explain me about the practical usage of LENGTH2,
LENGTH4, LENGTHB and LENGTHC functions? For example in
what scenario do we use LENGTH2 instead of LENGTH function
and so on?
Answer: The permutations of length
(lengthb, lengthc, length2, length2) are used primary with
special character sets, such as double-byte character sets.
The character set makes the difference when using the
lengthb and lengthc functions.
select *
from nls_database_parameters where parameter like '%SET%';
PARAMETER
VALUE
------------------------------
----------
NLS_CHARACTERSET
AL32UTF8
NLS_NCHAR_CHARACTERSET
AL16UTF16
AL32UTF8 is a mutibyte character set: a character can
take 1,2,3 or 4 bytes for storage. LENGTHB returns the
length in number of bytes and LENGTHC returns the length in
number of characters. You should set NLS_LANG character set
to the "the character set used by the client application
(normally the Oracle character set that corresponds to the
user's terminal character set or the OS character set).
The length can be returned in any of the following units:
- LENGTH returns Characters
- LENGTHB returns Bytes
- LENGTHC returns unicode characters
- LENGTH2 returns Code units
-
LENGTH4 returns Code points
As we have noted, LENGTH works best with
single-byte character sets, a multi-byte character may use
bytes or char semantics.
SELECT
LENGTHB(UNISTR('X')) LENGTHB,
LENGTHC(UNISTR('X')) LENGTHC
FROM
DUAL;
LENGTHB LENGTHC
---------- ----------
2 1
The lengthb function can be used to show the length in
bytes of a column name:
select
select lengthb(to_char(substr(mycol,1,4000)))
from
mytab;
In most cases with single-byte strings, the length2 and
length4 returns the same length value:
select
substr(table_name,1,20),
LENGTH(table_name),
LENGTH2(table_name),
LENGTH4(table_name)
from
user_tables
where
rownum < 10;
AUTHORS
7 7 7
DISCOUNTS 9 9
9
EMPLOYEE 8 8 8
JOBS
4 4 4
PUBLISHERS 10 10 10
PUB_INFO
8 8 8
ROYSCHED 8
8 8
SALES
5 5 5
STORES
6 6 6
The Toad world site shows this example using the LENGTH
function using single- and multibyte database character set.
SELECT
LENGTH('CANDIDE') "Length in
characters"
FROM
DUAL;
Length in
characters
--------------------
7
--
This example assumes a double-byte database character set.
SELECT
LENGTHB ('CANDIDE') "Length in
bytes"
FROM
DUAL;
Length in
bytes
---------------
14
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|