Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to create and test a function for counting characters and substrings in text.

-- SQL character count - SQL Server character count function

-- SQL user-defined function - UDF - SQL scalar function

-- SQL replace string function - SQL len function - string length

USE AdventureWorks;

GO

CREATE FUNCTION dbo.fnCharCount

               (@Text VARCHAR(MAX),

                @Char CHAR(1))

RETURNS INT

AS

  BEGIN

    DECLARE  @Occur INT

    SELECT @Occur = len(@Text) - len(replace(@Text,@Char,''))

    RETURN @Occur

  END

GO

-- Test UDF

-- Count commas in string

SELECT dbo.fnCharCount('This, is a, sample,,, with, commas', ',')

GO

-- 6

------------

-- SQL Server count substring occurrence in string

------------

USE AdventureWorks2008;

GO

CREATE FUNCTION dbo.fnSubstringCount

               (@Text NVARCHAR(MAX),

                @Substring NVARCHAR(MAX))

RETURNS INT

AS

  BEGIN

    DECLARE  @Occur INT

    SELECT @Occur = (len(@Text) - len(replace(@Text,@Substring,'')))/len(@Substring)

    RETURN @Occur

  END

 GO

SELECT * FROM

(SELECT ProductDescriptionID,

       SubstringCount=dbo.fnSubstringCount(Description,'frame')

FROM Production.ProductDescription ) x

WHERE SubstringCount > 1

ORDER BY ProductDescriptionID

/* ProductDescriptionID SubstringCount

      637               2

      642               3

      644               3

      647               3

      1020              2

*/

------------