SQL Functions   «Prev  Next»
Lesson 5 SQL LENGTH function
Objective Write a query that combines several special functions you learned earlier in this module.

SQL Length Function

The length of a string is important to know when lining up columns, formatting information, and generally working with strings. The function returns a number representing the overall length of the string.
This function is supported differently in different engines. You may see it as LEN or LENGTH. You will need to check with your server's documentation to see which implementation is used. In fact, some database engines do not support this function at all. For example, Microsoft's SQL Server expects that you will determine the length on the client-side of the query, not on the server-side.
If your engine does support LEN or LENGTH, the syntax is as follows:

SELECT LEN(MyColumn) 
FROM MyTable
or
SELECT LENGTH(MyColumn) 
FROM MyTable

In either case, you are provided with the length of the values in the MyColumn column, in a derived, unnamed column.

Sql Function - Exercise

Complete this exercise to practice mixing and matching functions.
SQL Function - Exercise

Finding the Length of a String with CHARACTER_LENGTH()

Use the function CHARACTER_LENGTH() to return the number of characters in a string. The function's important characteristics are:
  1. CHARACTER_LENGTH() returns an integer greater than or equal to zero.
  2. CHARACTER_LENGTH() counts characters, not bytes. A multibyte or Unicode character represents one character.
  3. The length of an empty string (' ' ) is zero.
  4. If its argument is null, CHARACTER_LENGTH() returns null.

To find the length of a string:

Type:
CHARACTER_LENGTH(string)
string is a string expression such as a column that contains character strings, a string literal, or the result of an operation or function that returns a string (Listings 4.51 and 4.52)

SELECT au_fname, CHARACTER_LENGTH(au_fname) 
AS "Len" FROM authors;
Listing 4.51 List the lengths of the authors' first names.

SELECT title_name,
  CHARACTER_LENGTH(title_name) AS "Len"
FROM titles
WHERE CHARACTER_LENGTH(title_name) < 30
ORDER BY CHARACTER_LENGTH(title_name) ASC;
Listing 4.52 List the books whose titles contain fewer than 30 characters, sorted by ascending title length.