No Split in SQL? Well, here's the method you need!

Splitting a string is an important work-around for not having array-type arguments to stored procedures and functions. Passing in a list of values for, say, a cross reference table value can save a lot of needless database calls by combining them in one and treating them as a single known quantity. Here's the code:

IF  EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[fn_split]'))
DROP FUNCTION [dbo].[fn_split]
GO

CREATE  FUNCTION fn_Split(@text varchar(max), @delimiter char(1) = ',')
RETURNS @TABLE TABLE
(    
  [position] int IDENTITY PRIMARY KEY,
  [value] varchar(max)   
)
AS
BEGIN
 
DECLARE @index int 
SET @index = -1 

WHILE (LEN(@text) > 0) 
  BEGIN  
    SET @index = CHARINDEX(@delimiter , @text)  
    IF (@index = 0) AND (LEN(@text) > 0)  
      BEGIN   
        INSERT INTO @TABLE VALUES (@text)
          BREAK  
      END  
    IF (@index > 1)  
      BEGIN   
        INSERT INTO @TABLE VALUES (LEFT(@text, @index - 1))   
        SET @text = RIGHT(@text, (LEN(@text) - @index))  
      END  
    ELSE 
      SET @text = RIGHT(@text, (LEN(@text) - @index)) 
    END
  RETURN
END
GO

select 
     position
    ,convert(varchar(10), value) as value
from 
    dbo.fn_Split('dog,cat,pig,cow,rat', default)
order by 
    value desc

/*

output

position    value
----------- ----------
5           rat
3           pig
1           dog
4           cow
2           cat


*/