SQL Function – Len ( )

The LEN functions return the length of char. LEN calculates length using characters as defined by the input character set. LENB uses bytes instead of characters. char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is of data type NUMBER. If char has data type CHAR, then the length includes all trailing blanks. If char is null, then this function returns null.

Restriction on LENB the LENB function is supported for single-byte LOBs only. It cannot be used with CLOB and NCLOB data in a multibyte character set

SQL Function Len( ) Syntax:

SELECT LEN(column_name) FROM table_name;

SQL Function Len( ) Example:

Assumed that we have a table named “Customer” given below.

CustomerID	CustomerName	Address	City	PostalCode	Country
1	Ann Devon	35 King George	London	WX3 6FW	UK
2	Roland Mendel	Kirchgasse 6	Graz	8010	Austria
3	Aria Cruz	Rua Orós, 92	São Paulo	05442-030	Brazil
4	Diego Roel	C/ Moralzarzal, 86	Madrid	28034	Spain
5	Martine Rancé	184, chaussée de Tournai	Lille	59000	France

We are going to select Customer Name, Address and Length of Address as LenAdd.

The syntax for Format:

SELECT CustomerName,Address, LEN(Address) as LenAdd

FROM Customers;

Output:

ContactName	Address	LenAdd
Ann Devon	35 King George	14
Roland Mendel	Kirchgasse 6	12
Aria Cruz	Rua Orós, 92	12
Diego Roel	C/ Moralzarzal, 86	18
Martine Rancé	184, chaussée de Tournai	24

SQL Function LenB( ) Syntax:

SELECT CustomerName,Address, LENB(Address) as LenAddInByte

FROM Customers;

Output:

ContactName	Address	LenAddInByte
Ann Devon	35 King George	28
Roland Mendel	Kirchgasse 6	24
Aria Cruz	Rua Orós, 92	24
Diego Roel	C/ Moralzarzal, 86	36
Martine Rancé	184, chaussée de Tournai	48