CHAR_LENGTH() in PostgreSQL

In PostgreSQL, the CHAR_LENGTH() function is used to return the number of characters in a string. This function is synonymous to LENGTH() function when dealing with text strings. Both functions serve the same purpose of finding the length of the string.

Syntax of CHAR_LENGTH() in PostgreSQL:

•  string: The string for which you want to determine the length.

CHAR_LENGTH() in PostgreSQL function is used in different scenarios

• Get Length of the character in PostgreSQL
• Filter rows based on the character length in PostgreSQL
• combined Length of two or more columns in PostgreSQL
• Calculate average string length of the column in PostgreSQL

Examples

Example 1: Basic Usage

To get the length of a specific string, we will be using CHAR_LENGTH() function

 

SELECT CHAR_LENGTH('PostgreSQL') AS char_length;

Output:

Example 2: Getting the Length of Column Values

Suppose you have a table “summerfruits” with a column colour, and you want to get the length of each colour:

 

select *,CHAR_LENGTH(colour) as colour_length from Summerfruits;

So the resultant table will be

Output:

Example 3: Filtering Based on String Length

You might want to filter rows based on the length of a string, For example in summerfruit table if you want to filter fruits with name more than 8 characters

 

select *,CHAR_LENGTH(name) as name_length from Summerfruits where CHAR_LENGTH(name) > 8;

using  CHAR_LENGTH() function to filter the based above condition , so the resultant table will be

Output:

Example 4: Length of Combined Strings – Combined length of two columns in PostgreSQL

If you want to find the length of combined strings, you can concatenate the strings and then get the length:

 

SELECT *, CHAR_LENGTH(name || ' - ' || shape) AS combined_length FROM Summerfruits;

using CHAR_LENGTH() function to find combined length of two columns “name” & “shape” , so the resultant table will be

Output:

Example 5: Calculating Average String Length of the column in PostgreSQL Table

If you want to find the Average length of string column , you can use CHAR_LENGTH() along with AVG() function

 

select *,
       AVG(CHAR_LENGTH(name)) over all_rows as avg_name_length
from Summerfruits
window all_rows as ();

The resultant table will be

Output: