Cacher is the code snippet organizer for pro developers

/* 

Procedure to separate out tablecard names from Event Listing report output. It focuses on the Notes column of a table called eventtest, which is where output CSV file was uploaded. This column has long character strings that include text betwen [ ] that contain table card names. There may be multiple table card names in-between those brackets - these multiple names will be delimited by commas.

Procedure has two main sections:

1. search Notes column and pull out all text between the brackets, store in @results table
2. from @results table, separate out comma-delimited names and place them into individual rows (one name per row) in @separatedresults table

*/

CREATE PROCEDURE lsp_sb_tablecards

-- declare variables for section 1
@lineID INT = 1,
@substart INT = 1,
@subend INT = 1,
@sublength INT = 1,

-- declare variables for section 2
@iterationstart INT = 1,
@iterationend INT = 0,
@iterationlength INT = 0,
@splitteriteration INT = 1,
@iterationendlength INT = 1,
@stringlength INT = 1

AS

SET NOCOUNT ON

-- create table for section 1 to hold string found between brackets from original Notes column
DECLARE @results TABLE (
lineID INT,
Name VARCHAR(200),
TableNo VARCHAR(200)
);

-- create table for section 2 to hold separated table column name found between commas in @results table
DECLARE @separatedresults TABLE (
lineID INT,
Name VARCHAR(200),
TableNo VARCHAR(200)
);

-- !!BEGIN SECTION 1!!

WHILE @lineiD <= (SELECT MAX(lineID) from eventtest)

	BEGIN

		SET @substart = (SELECT (SELECT CHARINDEX('[', Notes) FROM eventtest WHERE lineID=@lineID) + 1);
		SET @subend = (SELECT (SELECT (SELECT CHARINDEX(']', Notes) FROM eventtest WHERE lineID=@lineID) - 
			(SELECT CHARINDEX('[', Notes) FROM eventtest WHERE lineID=@lineID)) - 1);
		IF @subend < 0 SET @sublength = 0 ELSE SET @sublength = @subend;

		INSERT INTO @results (lineID, TableNo, Name)
		SELECT lineID, tableno,
			(SELECT SUBSTRING((SELECT Notes FROM eventtest WHERE lineID=@lineID), @substart, @sublength) 
			FROM eventtest WHERE lineID=@lineID) FROM eventtest WHERE lineID=@lineID;

		SET @lineID = @lineID + 1;

		IF @lineID > (SELECT MAX(lineID) FROM eventtest)
			BREAK
		ELSE
			CONTINUE
	END

-- reset @lineID to 1 so second part of procedure can begin
SET @lineID = 1

-- !!BEGIN SECTION 2!!

-- begin outer loop, iterates through @lineID so each row can be fed to the inner loop
WHILE @lineID <= (SELECT MAX(lineID) FROM @results)
BEGIN

	-- begin inner loop, iterates @splitteriteration 1 - 16 to pull out values in-between commas in Name field. Iterates 16 times since it's unlikely that there'll ever be 16 commas/strings in-between those commas to pull out
	WHILE @splitteriteration <= 16
	BEGIN

		SET @stringlength = (SELECT LEN(Name) FROM @results WHERE lineID =@lineID);
		IF @iterationstart = 0 SET @iterationstart = 1 ELSE SET @iterationstart = @iterationstart;

		SET @iterationend = (SELECT CHARINDEX(',', (SELECT Name FROM @results WHERE lineID=@lineID), (SELECT @iterationstart))) - (SELECT @iterationstart -1);
		IF @iterationend <= 0 SET @iterationend = @stringlength ELSE SET @iterationend = @iterationend;

		SET @iterationlength = (SELECT (SELECT (@iterationend + @iterationstart) - @iterationstart) - 1)
		IF @iterationlength < 0 SET @iterationlength = 0 ELSE SET @iterationlength = @iterationlength;

		INSERT INTO @separatedresults (lineID, TableNo, Name)
			SELECT lineID, TableNo, (
			SELECT SUBSTRING(
				(SELECT Name FROM @results WHERE lineID=@lineID),
				(SELECT @iterationstart), 
				(SELECT @iterationlength)
				) 
			) 
			FROM @results WHERE lineID = @lineID;

		SET @iterationstart = (SELECT (@iterationstart + @iterationlength) + 1);
		SET @splitteriteration = @splitteriteration + 1;

	END

-- iterate @lineID by 1 and reset all cumulative variables so inner and outer loop can start from beginning
SET @lineID = @lineID +1;
SET @splitteriteration = 1;
SET @iterationstart = 1;

END

-- !!RETURN FINAL RESULTS TO USER!!
SELECT Name AS 'Tablecard Name', TableNo, lineID FROM @separatedresults
WHERE Name <> ''
ORDER BY Name;

GO 

 

EXEC lsp_sb_tablecards   



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

CREATE PROCEDURE lsp_sb_tablecards

-- declare variables for section 1
@lineID INT = 1,
@substart INT = 1,
@subend INT = 1,
@sublength INT = 1,

-- declare variables for section 2
@iterationstart INT = 1,
@iterationend INT = 0,
@iterationlength INT = 0,
@splitteriteration INT = 1,
@iterationendlength INT = 1,
@stringlength INT = 1

AS

SET NOCOUNT ON

-- create table for section 1 to hold string found between brackets from original Notes column
DECLARE @results TABLE (
lineID INT,
Name VARCHAR(200),
TableNo VARCHAR(200)
);

-- create table for section 2 to hold separated table column name found between commas in @results table
DECLARE @separatedresults TABLE (
lineID INT,
Name VARCHAR(200),
TableNo VARCHAR(200)
);

-- !!BEGIN SECTION 1!!

WHILE @lineiD <= (SELECT MAX(lineID) from eventtest)

	BEGIN

		SET @substart = (SELECT (SELECT CHARINDEX('[', Notes) FROM eventtest WHERE lineID=@lineID) + 1);
		SET @subend = (SELECT (SELECT (SELECT CHARINDEX(']', Notes) FROM eventtest WHERE lineID=@lineID) - 
			(SELECT CHARINDEX('[', Notes) FROM eventtest WHERE lineID=@lineID)) - 1);
		IF @subend < 0 SET @sublength = 0 ELSE SET @sublength = @subend;

		INSERT INTO @results (lineID, TableNo, Name)
		SELECT lineID, tableno,
			(SELECT SUBSTRING((SELECT Notes FROM eventtest WHERE lineID=@lineID), @substart, @sublength) 
			FROM eventtest WHERE lineID=@lineID) FROM eventtest WHERE lineID=@lineID;

		SET @lineID = @lineID + 1;

		IF @lineID > (SELECT MAX(lineID) FROM eventtest)
			BREAK
		ELSE
			CONTINUE
	END

-- reset @lineID to 1 so second part of procedure can begin
SET @lineID = 1

-- !!BEGIN SECTION 2!!

-- begin outer loop, iterates through @lineID so each row can be fed to the inner loop
WHILE @lineID <= (SELECT MAX(lineID) FROM @results)
BEGIN

	-- begin inner loop, iterates @splitteriteration 1 - 16 to pull out values in-between commas in Name field. Iterates 16 times since it's unlikely that there'll ever be 16 commas/strings in-between those commas to pull out
	WHILE @splitteriteration <= 16
	BEGIN

		SET @stringlength = (SELECT LEN(Name) FROM @results WHERE lineID =@lineID);
		IF @iterationstart = 0 SET @iterationstart = 1 ELSE SET @iterationstart = @iterationstart;

		SET @iterationend = (SELECT CHARINDEX(',', (SELECT Name FROM @results WHERE lineID=@lineID), (SELECT @iterationstart))) - (SELECT @iterationstart -1);
		IF @iterationend <= 0 SET @iterationend = @stringlength ELSE SET @iterationend = @iterationend;

		SET @iterationlength = (SELECT (SELECT (@iterationend + @iterationstart) - @iterationstart) - 1)
		IF @iterationlength < 0 SET @iterationlength = 0 ELSE SET @iterationlength = @iterationlength;

		INSERT INTO @separatedresults (lineID, TableNo, Name)
			SELECT lineID, TableNo, (
			SELECT SUBSTRING(
				(SELECT Name FROM @results WHERE lineID=@lineID),
				(SELECT @iterationstart), 
				(SELECT @iterationlength)
				) 
			) 
			FROM @results WHERE lineID = @lineID;

		SET @iterationstart = (SELECT (@iterationstart + @iterationlength) + 1);
		SET @splitteriteration = @splitteriteration + 1;

	END

-- iterate @lineID by 1 and reset all cumulative variables so inner and outer loop can start from beginning
SET @lineID = @lineID +1;
SET @splitteriteration = 1;
SET @iterationstart = 1;

END

-- !!RETURN FINAL RESULTS TO USER!!
SELECT Name AS 'Tablecard Name', TableNo, lineID FROM @separatedresults
WHERE Name <> ''
ORDER BY Name;

GO 

 

EXEC lsp_sb_tablecards