Count substrings

count-substrings.sql

/*
Counts a number of substrings in the given string

    
Args:
    @string  
    @substring 
  
Returns:
    INT
	
Examples:
    select dbo.fncCountSubstrings('бацацацацацацаха', 'ац')
    -> 6
*/

CREATE OR ALTER FUNCTION [dbo].[fncCountSubstrings]
(
	  @string     NVARCHAR(max),
    @substring  NVARCHAR(100)
)
RETURNS INT
AS
BEGIN
    IF LEN(@substring) = 0 RETURN 0

    DECLARE @result INT = ( LEN(@string) - LEN(REPLACE(@string, @substring, '')) ) / LEN(@substring)
    RETURN @result
END