Instead of using a function, I'm hoping to use a CTE to split a string into individual records. I have most of it, except that the last record is the word-before last. (see sample results below for reference). You will notice that "delimiter" is the new rowdata, and "a" is the itemdata. I want another row that has "" as row data, and "delimiter" as itemdata.
DECLARE @RowData varchar(2000)
DECLARE @SplitOn varchar(1)
@RowData = 'This is a test for splitting using a CTE to break a varchar into records based on a delimiter',
@SplitOn = ' '
;WITH CTE (RowData, SplitOn, ItemData, Cnt) AS
SUBSTRING(@RowData, CHARINDEX(@SplitOn, @RowData) + 1, LEN(@RowData)),
Data, 1, CHARINDEX(@SplitOn, @RowData) - 1))),
SUBSTRING(RowData, CHARINDEX(SplitOn, RowData) + 1, LEN(RowData)),
ata, 1, CHARINDEX(SplitOn, RowData) - 1))),
Cnt + 1
FROM CTE B
CHARINDEX(SplitOn, RowData) > 0