See Also: Main_Page - Transact SQL Code Library - String Manipulation Functions

Count appearance of a particular string within the outer string

-- string manipulation procedure  
-- following procedure counts the occurence of the inner string in the outer string  
CREATE PROC COUNT_CHARACTERS
           @top_string   VARCHAR(400),
           @inner_string VARCHAR(50)
AS
  DECLARE  @i INT,
           @n INT
  
  SET @n = 0
  
  WHILE CHARINDEX(@inner_string,@top_string) <> 0
    BEGIN
      SELECT @i = CHARINDEX(@inner_string,@top_string)
      
      SET @top_string = SUBSTRING(@top_string,(@i + LEN(@inner_string) + 1),
                                  (LEN(@top_string) - @i))
      
      PRINT @top_string
      
      SET @n = @n + 1
    END
  
  IF @n > 0
    BEGIN
      SELECT 'the inner string is contained in the outer string ' + CAST(@n AS VARCHAR(4)) + ' time(s)'
    END
  ELSE
    BEGIN
      SELECT 'the inner string does not appear in the outer string'
    END

GO