Every now and then, I need to identify how many times a certain character appears in a string in SQL Server. Usually, I’ll do a few searches on Google and remind myself again that it’s not too difficult of a task. There’s not a built-in function to do this in SQL Server.
Let’s use one of the most popular pangrams in the English language to test this.
Let’s assume we want to count the letter “e” in this string. We can execute the following code:
DECLARE @String char (100); SET @String = 'The quick brown fox jumps over the lazy dog.'; SELECT DATALENGTH(@String) - DATALENGTH(REPLACE(@String, 'e', ''));
This will return 3 as our result.
So, you may have been wondering why I made that a char (100) data type. You may have also been wondering why I used the
DATALENGTH function rather than the
LEN function. The default behavior of the
LEN function will exclude trailing spaces. We need to use
DATALENGTH in the event we’re trying to identify those.
If this feature were native in SQL Server, I’m not even sure what it would be named. Maybe CHAR_COUNT? I’m not even sure if it would be necessary. Python has a string method, count(), that can perform this task.
Thanks for reading!