datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to create a function to count characters in a string?

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

*/

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

Exam Prep 70-461
Exam 70-461