Of course you can use a tally table with text values.
This is ugly because you can't use CHARINDEX() with text values and it assumes that the max length of a substring is 12 characters, but it works...and if something works then it can usually be made to look nicer and work better...
[font="Courier New"]CREATE PROCEDURE proc_Function_Explode_New
(@Delimiter CHAR(1),
@String TEXT)
AS
SELECT
CASE WHEN PATINDEX('%'+@Delimiter+'%', SUBSTRING(@String, number+1, 12)) > 0
THEN SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END,
PATINDEX('%'+@Delimiter+'%', SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END, 12))-1)
ELSE SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END,
DATALENGTH(SUBSTRING(@String, CASE number WHEN 1 THEN 1 ELSE number+1 END, 12))) END
FROM Numbers
WHERE number <= DATALENGTH(@String)
AND (SUBSTRING(@String, number, 1) = @Delimiter
OR number = 1)
ORDER BY number
RETURN [/font]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden