Cacher is the code snippet organizer for pro developers

-- SPLIT_STRING allows you to take a single string with some type of delimiter, and split that string into mutiple values in multiple rows. Format is SELECT value FROM STRING_SPLIT('delimited string to be split', 'character that is the delimiter that you want to split based on').

-- Basic example: 
SELECT value AS 'Result of String Split'
FROM STRING_SPLIT('this,will,get,split,into,rows,based,on,a,comma,as,a,delimiter', ',');

/*
Results of the basic example above:

Result of String Split
-------------------------------------------------------------
this
will
get
split
into
rows
based
on
a
comma
as
a
delimiter

(13 row(s) affected)

*/

-- Example using a SELECT statement to grab the initial string to be split up.
SELECT value AS 'Result of String Split'
FROM STRING_SPLIT(
(SELECT SUBSTRING((SELECT Notes from eventtest WHERE lineID=19),
	(SELECT (SELECT CHARINDEX('[', Notes) FROM eventtest WHERE lineID=19) + 1),
	(SELECT CHARINDEX(']', Notes) FROM eventtest WHERE lineID=19) - (SELECT (SELECT CHARINDEX('[', Notes) FROM eventtest WHERE lineID=19) + 1)
	) AS FullCardNames),
	',') 
WHERE RTRIM(value) <> ''
;

-- Example of taking the split result set and INSERTing INTO a table called splitrepository, into the Names column.
INSERT INTO splitrepository (Names)
SELECT value 
FROM STRING_SPLIT(
(SELECT SUBSTRING((SELECT Notes from eventtest WHERE lineID=19),
	(SELECT (SELECT CHARINDEX('[', Notes) FROM eventtest WHERE lineID=19) + 1),
	(SELECT CHARINDEX(']', Notes) FROM eventtest WHERE lineID=19) - (SELECT (SELECT CHARINDEX('[', Notes) FROM eventtest WHERE lineID=19) + 1)
	) AS FullCardNames),
	',') 
WHERE RTRIM(value) <> ''
;