T-SQL to find number of string occurrences with in a string

by Amol 16. February 2011 19:01

In SQL Server, there is no inbuilt function which counts number of string occurrences within a given string. To find this, we will have to use trick here.  First replace the search string with search string by appending one space to it and get the length of the string. And subtract the new length from original string length

Suppose we want to find string “O” from the string “MICROSOFT”, So replace “O” with “O “ (+ space), your string will becomes “MICRO SO FT”.

Here is the scalar function to this.

CREATE FUNCTION [Asset].[Occurrences]

(

@Expression NVARCHAR (MAX),

@SearchString NVARCHAR (256)

)

RETURNS INT

AS

BEGIN

 

    RETURN(

            LEN(REPLACE(

                         @Expression

                        ,@SearchString

                        ,@SearchString + ' ')

                       )

            - LEN(@Expression)

          )

 

END

Tags:

SQL Server Scripts

Category

Recent Posts

Tag cloud