PostgreSQL CHAR_LENGTH() function

CHAR_LENGTH() function

The PostgreSQL char_length function or character_length function is used to count the number of characters in a specified string. This built-in function is particularly useful for string manipulation and validation, ensuring data integrity, and optimizing storage. The CHAR_LENGTH() function is synonymous with the CHARACTER_LENGTH() function, and both can be used interchangeably.

Uses of CHAR_LENGTH() Function

• Data Validation: Ensures that the string data meets the required length criteria.


• String Manipulation: Assists in operations like substring extraction, concatenation, and formatting.


• Optimizing Storage: Helps in analyzing and optimizing the storage requirements for string data.


• Data Presentation: Useful in formatting and presenting string data in reports and user interfaces.


• Conditional Logic: Facilitates conditional operations based on the length of strings.


• Text Analytics: Supports text analytics by providing character count information.

Syntax:

char_length (string)

Parameters:

PostgreSQL Version: 9.3

• Compatible with PostgreSQL version 9.3 and later.

Pictorial Presentation of PostgreSQL CHAR_LENGTH() function

Example: PostgreSQL CHAR_LENGTH() function

SQL Code:

SELECT char_length('w3resource') AS "Length of a String";

Output:

 Length of a String
--------------------
                 10
(1 row)

PostgreSQL CHAR_LENGTH() function using Column :

Sample Table: employees.

If we want to display the first name, last name and concatenate of first name and last name and number of character after concatenation of those employee who belong to the dept 20 from the employee table, the following SQL can be executed:

SQL Code:

SELECT first_name,last_name,
first_name||' '||last_name AS "Name of Employee",
char_length(first_name||' '||last_name ) AS "Length of Name"
FROM employees
WHERE department_id=20;

Output:

 first_name | last_name | Name of Employee  | Length of Name
------------+-----------+-------------------+----------------
 Michael    | Hartstein | Michael Hartstein |             17
 Pat        | Fay       | Pat Fay           |              7
(2 rows)

Example: Using with TEXT Type data type

SQL Code:

SELECT char_length(CAST('hello' AS TEXT)) AS "Length of a String";

Output:

Length of a String|
------------------+
                 5|

Example: Using with CHAR Type data type

SQL Code:

SELECT char_length(CAST('hello' AS CHAR(10))) AS "Length of a String";

Output:

Length of a String|
------------------+
                 5|

Example: Using with VARCHAR Type data type

SQL Code:

SELECT char_length(CAST('hello' AS VARCHAR(10))) AS "Length of a String";

Output:

Length of a String|
------------------+
                 5|

Example: Using with CONCAT() Function

SQL Code:

SELECT char_length(CONCAT('hello', 'world')) AS "Length of a String";

Output:

Length of a String|
------------------+
                10|

Example: Using with SUBSTRING() Function

SQL Code:

SELECT char_length(SUBSTRING('hello world' FROM 1 FOR 5)) AS "Length of a String";

Output:

Length of a String|
------------------+
                 5|

Example: Using with TRIM() Function

SQL Code:

SELECT char_length(TRIM('  hello  ')) AS "Length of a String";

Output:

Length of a String|
------------------+
                 5|

Previous: BIT_LENGTH function
Next: LOWER function