This might give you a start, it was posted here on SCC but by whom my memory fails me.
DECLARE @string VARCHAR(50)--must be sized large enough for given string
DECLARE @Find AS INT
DECLARE @Char AS VARCHAR(5)
DECLARE @First AS INT
DECLARE @Rep AS VARCHAR(5)
DECLARE @position AS INT
-- Initialize the current position and the string variables.
SET @position = 1
SET @First = 1
SET @Rep = ''
SET @string = '22,4444,444,66,44,4,5,55,5555,55'
--SET @string = '~~F~T~G'
WHILE @position <= DATALENGTH(@string)
BEGIN
SET @Find = (SELECT ASCII(SUBSTRING(@string, @position, 1)))
/*--this is the ASCII value for the string separator in this case a comma
it may be any character */
IF @Find <> 44 BEGIN
SET @Char = (SELECT CHAR(ASCII(SUBSTRING(@string, @Position, (@position - @First) +1))))
SET @Rep = @Rep + @Char
END
ELSE
BEGIN
PRINT 'Rep found*' + @Rep + '*' --used only during testing
SET @First = @position
SET @Rep = ''
END
IF @position = DATALENGTH(@string)
BEGIN
IF @position - DATALENGTH(@string) > 0
BEGIN
SET @Char = (SELECT CHAR(ASCII(SUBSTRING(@string, @Position, (@position - DATALENGTH(@string))) )))
SET @Rep = @Rep + @Char
END
PRINT 'Last Rep found*' + @Rep + '*'--only used during testing
END
SET @position = @position + 1
END
For the string defined above the result is
Rep found*22*
Rep found*4444*
Rep found*444*
Rep found*66*
Rep found*44*
Rep found*4*
Rep found*5*
Rep found*55*
Rep found*5555*
Last Rep found*55*