Cacher is the code snippet organizer for pro developers

/*
Example of a stored procedure that takes a column of data that has comma delimited strings, and does the following:

1. uses CHARINDEX to pull out certain text that starts with '['' and ends with ']'
2. uses STRING_SPLIT to take that text, which contains comma delimited values, and break it up into separate values in separate rows
3. takes those broken up values and inserts them into a temporary #results table
4. SELECTs a distinct list of the Name column from the #results table so the user can see a list of all distinct names that are broken out

Uses a WHILE LOOP and the CHARINDEX and STRING_SPLIT functions.

An example of one of these rows with comma delimited values:

'Mr. and Mrs. Smith,Mr. Ventura,Ms. Rau'

STRING_SPLIT takes a value like that and breaks it into this result set, which is then inserted into the Name column of the #results table:

Mr. and Mrs. Smith
Mr. Ventura
Ms. Rau

*/

CREATE PROCEDURE lsp_splitr
@lineID INT = 1

AS

SET NOCOUNT ON

CREATE TABLE #results (
RowNo INT IDENTITY(1,1),
Name varchar(200));

WHILE (SELECT @lineiD) <= (SELECT MAX(lineID) from eventtest)
BEGIN
	INSERT INTO #results (Name)
	SELECT value
	FROM STRING_SPLIT((SELECT SUBSTRING((SELECT Notes from eventtest WHERE lineID=@lineID),
	(SELECT (SELECT CHARINDEX('[', Notes) FROM eventtest WHERE lineID=@lineID) + 1),
	(SELECT CHARINDEX(']', Notes) FROM eventtest WHERE lineID=@lineID) - (SELECT (SELECT CHARINDEX('[', Notes) FROM eventtest WHERE lineID=@lineID) + 1)
	)),
	',');

SET @lineID = @lineID + 1
IF (SELECT @lineID) > (SELECT MAX(lineID) from eventtest)
	BREAK
ELSE
	CONTINUE
END

	SELECT DISTINCT Name FROM #results
	ORDER BY Name;

GO 

EXEC lsp_splitr

-------------
--DETAIL!!!--
-------------

CREATE PROCEDURE lsp_splitr
@lineID INT = 1

AS

SET NOCOUNT ON

CREATE TABLE #results (
RowNo INT IDENTITY(1,1),
Name varchar(200));

WHILE (SELECT @lineiD) <= (SELECT MAX(lineID) from eventtest)
BEGIN
	INSERT INTO #results (Name)
	SELECT value
	FROM STRING_SPLIT((SELECT SUBSTRING((SELECT Notes from eventtest WHERE lineID=@lineID),
	(SELECT (SELECT CHARINDEX('[', Notes) FROM eventtest WHERE lineID=@lineID) + 1),
	(SELECT CHARINDEX(']', Notes) FROM eventtest WHERE lineID=@lineID) - (SELECT (SELECT CHARINDEX('[', Notes) FROM eventtest WHERE lineID=@lineID) + 1)
	)),
	',');

SET @lineID = @lineID + 1
IF (SELECT @lineID) > (SELECT MAX(lineID) from eventtest)
	BREAK
ELSE
	CONTINUE
END

	SELECT DISTINCT Name FROM #results
	ORDER BY Name;

GO 

EXEC lsp_splitr