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() and CHARACTER_LENGTH() functions in SQL are essentially equivalent; both functions return the number of characters in a string. The key difference lies in how they handle multi-byte characters: LENGTH() returns the number of bytes, while CHARACTER_LENGTH() returns the number of characters, making it more appropriate for multi-byte (Unicode) strings.
However, please note that their precise behavior can vary somewhat between different SQL dialects, so always consult the documentation for your specific SQL platform.
Here is an example of how to combine the LENGTH() and CHARACTER_LENGTH() functions in a single SQL query: 
SELECT 
    customer_name,
    LENGTH(customer_name) AS ByteLength,
    CHARACTER_LENGTH(customer_name) AS CharLength
FROM
    customers;

In this query, customer_name is a column from the customers table. The query will return a list of customers' names along with the byte length and character length of each name.
Remember, if you're working with a SQL dialect that doesn't support CHARACTER_LENGTH(), you might use LENGTH() instead, although be aware of the aforementioned limitation concerning multi-byte characters.

Importance of String Length

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.

Finding Length of 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)

Listing 4.51 List the lengths of the authors' first names 

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

Listing 4.52: List the books whose titles contain fewer than 30 characters, sorted by ascending title length. 
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.

Sql Function - Exercise

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