Friday, September 7, 2018

T-SQL – How to get the count of occurrence of a keyword in a string


If there is a requirement to check how many times a particular keyword comes in a string then below T-SQL Code will be helpful in getting occurrence count.

USE [MyDB]

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE FUNCTION [dbo].[CountOccurancesOfString]
(
    @searchString nvarchar(max),
    @searchTerm nvarchar(max)
)
RETURNS INT
AS
BEGIN
    return (LEN(@searchString)-LEN(REPLACE(@searchString,@searchTerm,'')))/LEN(@searchTerm)
END


GO

--Executing the function
SELECT [dbo].[CountOccurancesOfString]('Life is beautiful,Life is not about finding yourself. Life is about creating yourself. so live life.' ,'Life')



                        -----------------End of Article-------------

No comments:

Post a Comment