If you haven't seen STRING_SPLIT, it is pretty awesome, and something we have all been working with for many years, which you can read about in Erland Sommarskog's great article here along with many other concepts involved in sending in a set of data to a stored procedure.
As a quick starting example, consider the string of characters: A,B,C;D. Using a separator of ',' this will give you three separate values. The STRING_SPLIT function will return the multiple values that are parsed into rows in a tabular output.
DECLARE @StringToSplit varchar(30) = 'A,B,C;D'; --could be varchar(max)
Note: BOL https://msdn.microsoft.com/en-us/library/mt684588.aspx doesn't specify a output value length for columns, but when I added an "INTO hold" clause, the column length varied by input value, and was varchar when the variable was varchar, and nvarchar when it was nvarchar. The length of the output for my call is varchar(30).
But what about the two versions of an empty value? '' (zero-length/empty string) and NULL. My NULL sense told me that the NULL one would return a single row with NULL, and the empty string would return a single empty string row. Of course, I was wrong, and it makes sense why (a row of NULL would be really annoying, especially if you want to use the output as an exclusion list, because A NOT IN (SET(B,NULL)) always returns NULL, not TRUE. )
For example, say the output could include NULL. You could end up with something like the following, where even though the input value of A is not in the NOT IN list, no rows are returned:
FROM (VALUES ('A')) AS List(Col)
WHERE Col NOT IN ( SELECT *
FROM (VALUES ('B'), (NULL)) AS Excludes(Col) );
Change NULL to 'C' and you get the result you expect.
Ok, so let's look at the output. What does '' (empty-string) return?
DECLARE @StringToSplit varchar(30) = '';
Just as expected, a single row with an empty value (which is easier to see if you keep the rows affected message on for the example because in the next output, it looks the same, but is a different result.
(1 row(s) affected)
Any length of value between the commas will return a value. BOL notes that if you have spaces between separators, you will get outputs of various lengths, so you can do the following:
DECLARE @StringToSplit varchar(30) = ', , , , bob '; --could be varchar(max)
And you will get 5 rows, one empty, one single string, one string of ' bob ' etc. Using RTRIM and LTRIM you can deal with spaces in the front and back of your string. But what about NULL (and not the string value 'NULL' which will confuse as you are writing early in the morning)?
DECLARE @StringToSplit varchar(30) = NULL;
(0 row(s) affected)
A NULL input returns 0 rows, which makes some sense, since there is no string value to split, but as everything with NULL, may not be exactly what you expect. In this case, the output is more than likely what you will have wanted in the first place, which definitely is not always the case with NULL.