Do you have a table in SQL Server that has a column with a string that has several parts separated with commas? How many commas are there? How many parts are in each string? Do you simply have a column in a table where you need to know how many occurrences there are of a particular character?

This post is grouped with the string parsing posts because before you choose which string parsing method you want to use you need to know if there are four or fewer occurrences so that you may choose to use the PARSENAME method discussed in the post called T-SQL Parse a String 2.

This post will show you how to create a function that returns that number. You can then store that number in the table. This code is from SQL Server Helper’s article called Count Character Occurrences Function. I’m using the article’s second variant.

Here we create a new user-defined function. It is fairly simple. If we take the length of the original string and deduct the length after we remove the search characters, we get the number of search characters. In SSMS, our new function is located under the database name, under Programmability, under Functions, under Scalar-valued Functions.

CREATE FUNCTION [dbo].[ufn_CountChar] ( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN

RETURN (LEN(@pInput) - LEN(REPLACE(@pInput, @pSearchChar, '')))

END
GO

How do you use this function? Suppose you have a table called MyTable and a column called List. The column has strings in it. Many of the strings are separated with commas.

UPDATE MyTable SET NumberOfParts = dbo.ufn_CountChar(List, ',') + 1

Try It Yourself

Here is an unfinished SQL script that you might run in your non-production server. First, scroll down and have a look at the screen shot.

IF OBJECT_ID(N'[dbo].[StringParse]', N'U') IS NOT NULL
DROP TABLE [dbo].[StringParse];
GO
CREATE TABLE [dbo].[StringParse](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[List] [nvarchar](50) NULL,
	[FirstOne] [nvarchar](50) NULL,
	[SecondOne] [nvarchar](50) NULL,
	[ThirdOne] [nvarchar](50) NULL,
	[ForthOne] [nvarchar](50) NULL,
	[FifthOne] [nvarchar](50) NULL,
	[NumberOfParts] [int] NULL,
 CONSTRAINT [PK_StringParse] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[StringParse] ON 
GO
INSERT [dbo].[StringParse] ([Id], [List], [FirstOne], [SecondOne], [ThirdOne], [ForthOne], [FifthOne], [NumberOfParts]) VALUES (1, N'a,b,c,d,e', NULL, NULL, NULL, NULL, NULL, 5)
GO
INSERT [dbo].[StringParse] ([Id], [List], [FirstOne], [SecondOne], [ThirdOne], [ForthOne], [FifthOne], [NumberOfParts]) VALUES (2, N'one,two,three,four,five,six', NULL, NULL, NULL, NULL, NULL, 6)
GO
INSERT [dbo].[StringParse] ([Id], [List], [FirstOne], [SecondOne], [ThirdOne], [ForthOne], [FifthOne], [NumberOfParts]) VALUES (3, N'Bob', NULL, NULL, NULL, NULL, NULL, 1)
GO
SET IDENTITY_INSERT [dbo].[StringParse] OFF
GO

UPDATE StringParse SET NumberOfParts = dbo.ufn_CountChar(List, ',') + 1
GO
SELECT * FROM [dbo].[StringParse]

UPDATE StringParse SET FirstOne = REVERSE(PARSENAME(REPLACE(REVERSE(List), ',', '.'), 1)) WHERE NumberOfParts < 5;
UPDATE [StringParse] SET SecondOne = REVERSE(PARSENAME(REPLACE(REVERSE(List), ',', '.'), 2)) WHERE NumberOfParts < 5;
UPDATE [StringParse] SET ThirdOne = REVERSE(PARSENAME(REPLACE(REVERSE(List), ',', '.'), 3)) WHERE NumberOfParts < 5;
UPDATE [StringParse] SET ForthOne = REVERSE(PARSENAME(REPLACE(REVERSE(List), ',', '.'), 4)) WHERE NumberOfParts < 5;


-- I want the fifth group here.
select substring([List], CHARINDEX(',',[List],(charindex(',',[List],charindex(',',[List],charindex(',',[List])+1)+1)+1)+1) + 1, LEN([List]) - CHARINDEX(',',[List],(charindex(',',[List],charindex(',',[List],charindex(',',[List])+1)+1)+1)+1))
AS 'Fifth part only if exactly 5 parts'
FROM [StringParse]
WHERE NumberOfParts = 5
GO
UPDATE [StringParse]
SET FifthOne = substring([List], CHARINDEX(',',[List],(charindex(',',[List],charindex(',',[List],charindex(',',[List])+1)+1)+1)+1) + 1, LEN([List]) - CHARINDEX(',',[List],(charindex(',',[List],charindex(',',[List],charindex(',',[List])+1)+1)+1)+1))
WHERE NumberOfParts >= 5
GO
SELECT * FROM [dbo].[StringParse]

Below is a screenshot of the results of the above script rin in SSMS.