Or press ESC to close.

How to Count Characters in SQL

21 Jun 2022 4 Min read
sql logo

In order to count specific characters in SQL, we need to use a special function LEN( string_expression ). This function is supported in SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, Analytics Platform System (PDW), Oracle, and MsSQL Server. In MySQL, there is an analog of this function called LENGTH(), and it has the same syntaxis and conditions for usage.

The problem I can run into using the function LENGTH() or any other variations of this function is that this function does not work without a parameter. As a parameter, we can use any one argument, and the argument here will be the name of the column where your characters are located. The type of the character could be CHAR, VARCHAR, INTEGER, DATE, and even ENUM. If we insert two different columns using a comma sign, an error with a wrong parameter occurs.

Step 1. For our test, we will use the following table:

CREATE TABLE IF NOT EXISTS test(id INT AUTO_INCREMENT, name VARCHAR(50), last_name VARCHAR (50), PRIMARY KEY (id) );

INSERT INTO test1.test (name, last_name) VALUES ('Georgi', 'Facello'), ('Bezalel', 'Simmel'), ('Parto', 'Bamford'), ('Chirstian', 'Koblick'), ('Kyoichi', 'Maliniak') ;

-- General view of the table: SELECT * FROM test;

id name last name
 1 Georgi   Facello
 2 Bezalel  Simmel 
 3  Parto Bamford 
 4  Chirstian Koblick 
 5  Kyoichi  Maliniak

Step 2. Here we can overview the function itself. The following example results in counting the number characters respectively to the cell our function refers to:

-- Syntaxis & example:

2.1

SELECT LENGTH(colum_name) FROM table_name;

SELECT id, name, LENGTH(name) FROM test;

id name LENGTH(name)
 1 Georgi  6
 2 Bezalel  7
 3  Parto 5
 4  Chirstian 9
 5  Kyoichi 7

-- 2.2 The same function could be written in another way:

SELECT id, name, OCTET_LENGTH(name) FROM test;

SELECT id, name, CHAR_LENGTH(name) FROM test;

SELECT id, name, CHARACTER_LENGTH(name) FROM test;

-- 2.3 Here, we also can find a function that returns the number of bits in the referred cell:

SELECT id, name, BIT_LENGTH(name) FROM test;

id name BIT LENGTH(name)
 1 Georgi  48
 2 Bezalel  56
 3  Parto 40
 4  Chirstian 72
 5  Kyoichi 56

Also, we can use LENGTH() in WHERE statement to find a specific number of characters SELECT colum_name1, colum_name2, ... FROM table_name WHERE LENGTH(colum_name) >=/<=/=/!= ... ;

SELECT id, name, LENGTH(name) FROM test WHERE LENGTH(name) = 6;

id name LENGTH(name)
1  Georgi 6

-- 2.4 In order to count the number of characters in multiple rows, we can use a CONCAT function:

CONCAT(colum_name1, colum_name2) – Here, we need to insert a couple of columns that we are interested in counting characters.

SELECT LENGTH(CONCAT(colum_name1, colum_name2)) FROM table_name;

SELECT id, CONCAT(name,last_name), LENGTH(CONCAT(name,last_name)) FROM test;

id name LENGTH(CONCAT(name, last name))
1  GeorgiFacello 13
BezalelSimmel  13
3 PartoBamford   12
ChirstianKoblick   16
KyoichiMaliniak   15

-- 2.5 We can count the maximum number of symbols in a column using the ORDER BY statement:

SELECT LENGTH(colum_name) FROM table_name ORDER BY LENGTH(colum_name) DESC(max-min)/ASC(min-max) LIMIT ... /without LIMIT;

SELECT id, name, LENGTH(name)

FROM test

ORDER BY LENGTH(name) DESC;

/* without LIMIT

id name LENGTH(name)
4 Chirstian 9
Bezalel 7
5 Kyoichi 7
Georgi  6
Parto 5

-- 2.6 The following example with the LIMIT function represents the result of the search for the row with the lowest number of characters:

*/ SELECT id, name, LENGTH(name) FROM test ORDER BY LENGTH(name) DESC LIMIT 1;

/* with LIMIT

id name LENGTH(name)
Chirstian 9

-- 2.7 Also, we can do that with an aggregate function to find MAX/MIN(column_name) among the whole table:

SELECT MAX/MIN(LENGTH(colum_name)) FROM table_name;

SELECT MAX(LENGTH(name)) FROM test;

MAX(LENGTH(name))
9

-- 2.8 Using a COUNT statement, we can find the total number of rows in our table:

SELECT COUNT(*) FROM table_name;

SELECT COUNT(*) FROM test;

COUNT(*)
5

-- 2.9 It also could be rewritten with the DISTINCT statement, which selects all unique rows in our column:

SELECT COUNT(DISTINCT colum_name) FROM table_name;

SELECT COUNT(DISTINCT name) FROM test;

COUNT(DISTINCT(name))
 5

-- 2.10 If we need to count all words in a row, we need to use the following code. It represents the subtraction of the length of a term by replacing an order of the comments and adding "1". The REPLACE() function returns all text in substring within a new substring.

--General syntaxis:

SELECT COUNT(LENGTH(column_name) - LENGTH(REPLACE(column_name, '',"))+1) FROM table_name;

SELECT id, (LENGTH(name) - LENGTH(REPLACE(name, '',"))+1) FROM test;

id LENGTH(name) - LENGTH(REPLACE(name, '', ")) + 1)
1

Conclusion

We hope you enjoyed our tutorial about how to count characters using SQL. Remember, if you ever need another way to count characters you are always welcome to copy and paste your text into the character counter on our home page.