Using SQL to get the number of occurrences of a character in a string in SQL Server
This SQL Snippet can be used to get the count of a single character, or an entire phrase, in a string. We have a seperate post for doing the same thing in Oracle.
Assume you have the string “a,b,c,d,e” and you want the count of commas (“,”) contained in that string. Just use the SQL below:
Select len('a,b,c,d,e') – len(replace('a,b,c,d,e', ',', "))
Running the query above returns 4, the number of commas in the string.
This can easily be converted into a reusable function as follows:
CREATE FUNCTION dbo.NumberOfOccurrences
(
@theString varchar(8000),
@theCharacter varchar(8000)
)
RETURNS int
AS
BEGIN
— Declare the return variable here
DECLARE @characterCount int
SELECT @characterCount = LEN(@theString) – LEN(REPLACE(@theString, @theCharacter, "));
— Return the result of the function
RETURN @characterCount
END
GO
Then you can simply call the function as so:
select dbo.NumberOfOccurrences('a,b,c,d,e', ',')
How does it work?
For the example above, we are just calculating the difference between the string length and the length of the string with the characters we are searching for, commas in the example above, removed.
The first LEN function gives the length of the original string which is 9. The REPLACE function replaces the commas with nothing giving the string “abcde”. The second LEN function gives the length of the ‘replaced’ string, which is 5. Then we subtract 5 from 9 giving 4; the number of commas in the string.
This does not have to work on a single character. It will also work on a string of any length. You just need to update the second parameter of the replace function.
For a discussion of this problem for Oracle, click here.
Comments are closed.
[…] need to know the level of the current task, you can use the method discussed in a separate article here to get the number of separators in the […]