PostgreSQL - Getting the Character Count

To check the number of characters, use the length function. This function returns the number of characters, not bytes, so it returns the same result regardless of whether the characters are half-width or full-width.

SELECT length(<column name>) FROM <table name>

Arguments and Return Value of the length Function

Number of characters = length(text)
text Target column name or string literal
Return value The number of characters in the given text or null
Type of return value integer

Examples of Using the length Function

Counting the Number of Characters in a String Literal

The following sample code displays the number of characters in the string literals ‘1234567890’ and ‘１２３４５６７８９０’. These are half-width and full-width characters respectively, but since the length function returns the number of characters, both results are 10. 
SELECT length('1234567890')
      ,length('１２３４５６７８９０');

Counting the Number of Characters in Text Stored in a Table

The following sample code displays the number of characters in the ‘soft_name’ column of the ‘sample_table’ table. 
SELECT *
       ,length(soft_name)
  FROM sample_table;

Using as a Search Condition

You can use the length function as a search condition to search for records that match a specific number of characters. However, indexes do not work, so if there are many records, it may take time to process. If you frequently search using the number of characters, you will get the number of characters at the time of data insertion (insert) and store it, and create an index on that column.

The following sample code extracts records where the number of characters in the ‘soft_name’ column of the ‘sample_table’ table is 10.

SELECT soft_name
  FROM sample_table
 WHERE length(soft_name) = 10;

Using for Sorting

If you use the length function in the ORDER BY clause, you can sort records in order of the number of characters. However, indexes do not work, so if there are many records, it may take time to process.

The following sample code sorts the records in the ‘sample_table’ table in order of the number of characters in the ‘soft_name’ column and displays the results.

SELECT *
  FROM sample_table
 ORDER BY length(soft_name);

Notes on Using the length Function

The length function returns null, not 0, when null is given as an argument. If you want to return 0, use the COALESCE function to replace null with 0.

Here is an example of using the COALESCE function to replace the result of the length function with 0 when it is null.

SELECT COALESCE(length(null), 0);

References

Test Environment