Counting Character Instances in a SQL String

I discovered this little trick today.

If you’re wanting to count the number of characters present in a given SQL Server varchar variable, or common, use the following convention

--assuming a search character contained in the variable @SeekChar

--from a variable
SELECT LEN(@StringVariableName) - LEN(REPLACE(@StringVariableName,@SeekChar,''))

--from a table field
SELECT LEN(SomeTableCol) - LEN(REPLACE(SomeTableCol, @SeekChar, ''))
FROM SomeTable

--The Oracle  PL/SQL Variant (for the hell of it).
--All we have to do is replace the LEN function with LENGTH!
SELECT LENTH(SomeTableCol)-LENGTH(REPLACE(SomeTableCol, @SeekChar, ''))
FROM SomeTable

Quick and easy and no annoying looping constructs.

Leave a Reply