Oracle String/Char Function - Oracle/PLSQL LENGTH Function

This Oracle tutorial explains how to use the Oracle/PLSQL LENGTH function.

The Oracle/PLSQL LENGTH function returns the length of the specified string.

LENGTH returns the length of a string. The returned value is the number of the characters in a string.

The general format for this function is:

LENGTH(string)

Syntax

The syntax for the Oracle/PLSQL LENGTH function is:

LENGTH( string1 )

string1 is the string to return the length for.

If string1 is NULL, then the LENGTH function will return NULL.

LENGTH(NULL)
------------
        NULL
 
 LENGTH('')
-----------
       NULL

Example


SQL> SELECT LENGTH('this is a test') FROM dual;

LENGTH('THISISATEST')
---------------------
                   14

SQL>

The following query gets the length of the employee's name:


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                      ENAME VARCHAR2(10),
                      HIREDATE DATE);
--   www .j  a  v a2 s.  c o m
INSERT INTO EMP VALUES (1, 'SMITH', TO_DATE('17-DEC-1980', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (2, 'ALLEN', TO_DATE('20-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (3, 'WARD',  TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (4, 'JONES', TO_DATE('2-APR-1981',  'DD-MON-YYYY'));
INSERT INTO EMP VALUES (5, 'MARTIN',TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));


SQL> select ename, length(ename) from emp;

ENAME      LENGTH(ENAME)
---------- -------------
SMITH                  5
ALLEN                  5
WARD                   4
JONES                  5
MARTIN                 6

SQL>

Length of a number

The decimal point (.) is counted in the number.


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
-- from   www.ja va2  s  .  c  om
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500.12, 20);

SQL> select sal, length(sal) from emp;

       SAL LENGTH(SAL)
---------- -----------
       800           3
      1600           4
      1250           4
      2975           4
      1250           4
      2850           4
      2850           4
      3000           4
      3000           4
      1500           4
   1500.12           7

11 rows selected.

SQL>