Counting Instances of Character in SQL Column

1. Introduction

In this tutorial, we’ll learn how to count the occurrences of a character in an SQL string.

We tested the code on Baeldung’s simple University database in MySQL 8.0.41, PostgreSQL 14.15, and SQL Server 2022.

2. MySQL

Unfortunately, MySQL has no built-in function to count a character in a string. To get the count, we can:

• delete all the occurrences of the character
• compute the length of the remainder
• subtract it from the length of the entire string

Schematically:

CHAR_LENGTH(string) - CHAR_LENGTH(REPLACE(string, character, ''))

The difference is equal to the number of occurrences of the given character. For example:

String: "Marc James"
Character: 'a'

Total Length: 10
String Without 'a': "Mrc Jmes"
Length: 8

Count = 10 - 8 = 2

Let’s count the character ‘a’ in the students’ names:

SELECT name,
       REPLACE(name, 'a', '') AS modified_name, 
       CHAR_LENGTH(name) - CHAR_LENGTH(REPLACE(name, 'a', '')) AS char_count
FROM Student;

Here’s the result:

# name	        modified_name	char_count
John Liu	John Liu	0
Rita Ora	Rit Or	        2
Philip Lose	Philip Lose	0
Samantha Prabhu	Smnth Prbhu	4
Vikas Jain	Viks Jin	2
...

REPLACE(name, ‘a’, ”) replaces all occurrences of character ‘a’ in the column name with an empty string, i.e., deletes them, and CHAR_LENGTH() returns the number of characters in a string. We can also use LENGTH(), but since it returns the number of bytes, we’ll get the correct results only if each character takes one byte.

3. PostgreSQL

The same query we used in MySQL works in PostgreSQL:

SELECT name,
       REPLACE(name, 'a', '') AS modified_name, 
       CHAR_LENGTH(name) - CHAR_LENGTH(REPLACE(name, 'a', '')) AS char_count
FROM Student;

# name	        modified_name	char_count
John Liu	John Liu	0
Rita Ora	Rit Or	        2
Philip Lose	Philip Lose	0
Samantha Prabhu	Smnth Prbhu	4
Vikas Jain	Viks Jin	2
...

However, PostgreSQL allows another approach:

1. split the string using the given character as the separator to get an array of strings
2. subtract one from the array length

The difference is equal to the number of occurrences of the given character. Schematically:

ARRAY_LENGTH(REGEXP_SPLIT_TO_ARRAY(string, character), 1) - 1

For example:

String: "Marc James"
Character: 'a'

Split: ["M", "rc J", "mes"]
Length: 3

Count = 3 - 1 = 2

Here’s how to count the occurrences of  ‘a’ using this method in PostgreSQL:

SELECT name,
       ARRAY_LENGTH(REGEXP_SPLIT_TO_ARRAY(name, 'a'), 1) - 1 AS char_count
FROM Student;

The results are correct:

name	        char_count
John Liu	0
Rita Ora	2
Philip Lose	0
Samantha Prabhu	4
Vikas Jain	2
...

Here:

• REGEXP_SPLIT_TO_ARRAY(name, ‘a’) splits name using ‘a’ as the separator
• ARRAY_LENGTH(…, 1) counts the elements in the array considering its first dimension

4. SQL Server

In SQL Server, we use LEN() instead of LENGTH() to determine the length of a string. The rest of the query that replaces the given character with ” remains the same:

SELECT name,
       REPLACE(name, 'a', '') AS modified_name, 
       LEN(name) - LEN(REPLACE(name, 'a', '')) AS char_count
FROM Student;

However, there’s a special case we must cover. REPLACE() in SQL Server trims the end of the string, so the result may be incorrect if we want to count the spaces:

SELECT LEN('Marc James ') - LEN(REPLACE('Marc James ', ' ', ''));

The result will be one instead of 2 because the trailing space got trimmed. The fix is to append a non-space character before replacing:

SELECT LEN('Marc James ' + '#') - LEN(REPLACE('Marc James ' + '#', ' ', ''));

Here, we used ‘#’ as that character. The result was correct: 2.

5. Discussion

5.1. Case Sensitivity

The approaches we showed don’t consider case sensitivity directly.

In the case-sensitive approach, the number of occurrences of ‘A’ in ‘Marc James’ is 0. However, in the case-insensitive approach, the correct answer is two.

The sensitivity of REPLACE() in MySQL and SQL Server depends on the collation, whereas the function is case insensitive in PostgreSQL.

If we want to ensure the approach is case insensitive, we can convert all the letters in the said column to lower or all to the upper cases. Additionally, we should use the same case for the target character.

To make REGEXP_SPLIT_TO_ARRAY() case-insensitive, we specify the ‘i’ option:

SELECT name,
       ARRAY_LENGTH(REGEXP_SPLIT_TO_ARRAY(name, 'A', 'i'), 1) - 1 AS char_count
FROM Student;

Without ‘i’, the results won’t be correct.

5.2. REPLACE() or REGEXP_REPLACE()?

MySQL and PostgreSQL also offer REGEXP_REPLACE() to replace all matches of a regex pattern with a new string.

In our case, however, this function would be an overkill. It would make more sense to use it if we wanted to count the occurrences of a pattern, not a single character.

6. Conclusion

In this article, we showed how to count a character in a column in MySQL, PostgreSQL, and SQL Server.

In all three SQL dialects, we can remove the character, determine the remainder’s length, and subtract it from the length of the entire string. In PostgreSQL, we can also split the column using the character as the delimiter, and subtract one to get the number of its occurrences.