ORACLE LENGTH - Online Tech Support

This tutorial page is based on examples. The Oracle Length Function returns a number of characters given by the input parameter. There are five types of functions to get the length of your string. The most common function is LENGTH and other four are LENGTHB, LENGTHC, LENGTH2 and LENGTH4. The functions are using different methods like LENGTH is counting characters and LENGTHB counting bytes. LENGTHC is counting the Unicode type of characters. The LENGTH2 is counting UCS2 types and LENGTH4 is UCS4-s. They all have only one input parameters and they are returning an integer value. The Oracle Length syntax is following:

LENGTH(<text>)

The first example is the most common way to count characters and the input word is name “EMMA“. The name contains 4 letters so the function returns number “4” as on the following output.

 SELECT LENGTH('EMMA')
   FROM DUAL;

The length function can be used for more things than only counting string length for example you can use it for counting a single character. The following example has written with using two queries and it demonstrates how to count “M” characters in name “EMMA“. The last query returned number “4” and that is amount of characters in the name. To find out the easiest way how many M-letters we do have we’ll just replace all “M“-s with double M-s like “MM“. Take a look at the following SQL query to see how to use the Oracle Replace function with Oracle Length.

 SELECT LENGTH(REPLACE('EMMA','M','MM')) duplicate_m
   FROM DUAL;

The output above became “6” and this does mean two letters more than the first query. When we’ll subtract from the last result the original one the result becomes “2” and that is out amount of “M“-s in the name (6 – 4 = 2). To put the words in a SQL query it becomes as follow.

 SELECT LENGTH(REPLACE('EMMA','M','MM')) - LENGTH('EMMA') find_m
   FROM DUAL;

The output of last query is number “2” and this confirms we have two “M“-s in name “EMMA“. The Oracle Length can be used in various ways to make our query easier and simpler.

The next example shows how works the Oracle LENGTHB function. We are going to use name “EMMA” to see what the function returns. Take a look at the following example.

 SELECT LENGTHB('EMMA')
   FROM DUAL;

The output has returned number “4” and this doesn’t look so different from the Oracle LENGTH function. The reason for the same amount is in the byte system. The name “EMMA” is using a single-byte characters and to every character responds one byte so the name is 4 byte long.

To see how LENGTHB works with a double-byte character we will need to use a double-byte character. We will add an Estonian letter to the name or maybe we shouldn’t call it as a name anymore. The word looks as “EMMAÜ” and the “Ü” character looks quite unusual but not to Estonians. The next select query shows how many characters the LENGTH returns with our new made-up “word”.

 SELECT LENGTH('EMMAÜ') duplicate_m
   FROM DUAL;

The LENGTH returned number “5” as we do have five characters in our word. Now has left to try LENGTHB with the same word and to see what does come out from there.

 SELECT LENGTHB('EMMAÜ')
   FROM DUAL;

The SQL returned number “6” and not “5” as the previous select. In other words, word “EMMA” takes “4” bytes and the strange “Ü” character “2” bytes. That makes 4 + 2 = 6. Did you like it? Now let’s play Estonians and take a guess how many bytes are in text “ümera öö ülestõus” and sorry no picture is here.

 SELECT LENGTHB('ümera öö ülestõus')
   FROM DUAL;

I guess it’s not easy to be an Estonian.