Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. Single Row Character Functions
  5. LENGTH

LENGTH

  • LENGTH is a single row character function.
  • LENGTH takes a string or numeric value as argument and returns the length.
  • If the argument is NULL then LENGTH function returns NULL.
  • NULL and empty string in Oracle both are treated as NULL. So, both returns NULL when given as argument to LENGTH.
  • White space is a valid character and LENGTH function counts white spaces as well.
  • Returns a NUMBER type.

Syntax: LENGTH(Character String) or LENGTH(Numeric)

SELECT 
    'The Code Man' Str, 
    LENGTH('The Code Man') StrLength
  FROM DUAL;

STR           STRLENGTH
------------ ----------
The Code Man         12
  • LENGTH function considers spaces as valid characters and counts them to find the length of the input string.
SELECT
    '   The Code Man ' Str, 
    LENGTH(' The Code Man ') StrLength
  FROM DUAL;

STR               STRLENGTH
---------------- ----------
   The Code Man          14
  • LENGTH function can be used to find length for numeric values.
SELECT 12432 Numeric_val, LENGTH(12432) NumLength 
  FROM DUAL;

NUMERIC_VAL  NUMLENGTH
----------- ----------
      12432          5
  • White spaces are not counted to find length of a numeric value.
SELECT 12432 Numeric_val, LENGTH( 12432   ) NumLength
  FROM DUAL;

NUMERIC_VAL  NUMLENGTH
----------- ----------
      12432          5
  • White spaces are counted to find length in a character string.
SELECT '12432   ' Value, LENGTH('12432   ') ValueLen
  FROM DUAL;

VALUE      VALUELEN
-------- ----------
12432             8
  • LENGTH function does not handle NULLs and returns NULL.
SELECT NULL Value, LENGTH(NULL) ZeroLength
  FROM DUAL;

V ZEROLENGTH
- ----------

   
SELECT '' Value, LENGTH('') ZeroLength 
  FROM DUAL;

V ZEROLENGTH
- ----------

   
Was this article helpful to you? Yes No

How can we help?