In this post I’ll discuss how to parse a string in SQL Server so that you can move your data over to new columns. This post is similar to the post called Multiple Genres in Genres Column. That post handles up to four parts in the string. This post discusses five and above.

If your strings have at most four parts, it’s easier to follow along with the above mentioned post and use PARSENAME. We discussed PARSENAME in our post called Multiple Genres in Genres Column.

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, NULL)
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, NULL)
GO
SET IDENTITY_INSERT [dbo].[StringParse] OFF
GO

-- 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))
FROM [MikeTest].[dbo].[StringParse]

-- I want the sixth group here.
select substring([List], charindex(',',[List],CHARINDEX(',',[List],(charindex(',',[List],charindex(',',[List],charindex(',',[List])+1)+1)+1)+1)+1) + 1, LEN([List]) - charindex(',',[List],CHARINDEX(',',[List],charindex(',',[List],charindex(',',[List],charindex(',',[List])+1)+1)+1)+1)+1)
FROM [MikeTest].[dbo].[StringParse]