Sql Splitting Function

Question

I have a string like that "10*cat*123456;12*rat*789;15*horse*365" i want to split it to be like that "cat, rat, horse" I have made this Function

CREATE FUNCTION [dbo].[Split](@BenNames VARCHAR(2000))
RETURNS VARCHAR(2000)
AS
BEGIN
    DECLARE @tmp VARCHAR(2000)
    SET @tmp = @BenNames    
    SET @tmp = SUBSTRING(
            SUBSTRING(@tmp, CHARINDEX('*', @tmp) + 1, LEN(@tmp)),
            0,
            CHARINDEX('*', SUBSTRING(@tmp, CHARINDEX('*', @tmp) + 1, LEN(@tmp)))
        )
    RETURN @tmp    but it only split only one part "10*cat*123456"

i want to send every part to this by another function or another looop

Answer 1

Have you taken a look at: http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx