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 |
| 2 | BezalelSimmel | 13 |
| 3 | PartoBamford | 12 |
| 4 | ChirstianKoblick | 16 |
| 5 | 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 |
| 2 | Bezalel | 7 |
| 5 | Kyoichi | 7 |
| 1 | Georgi | 6 |
| 3 | 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) |
|---|---|---|
| 4 | 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 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 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.