|
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
*/
------------ |
