Split comma seperated string values

Few days back I was told to write a stored procedure which would accept comma separated string as an input. The requirement was such that the values from this comma separated string had to be compared/combined with table later. 

The first thing I did was, to search for a function over internet, which would segregate the comma separated string into values. That search showed me many links which had a function written to address this task. Few of them are listed at the bottom of this article.

I used the third one (from the list) and completed the procedure.

Then few days later when I had some spare time, I thought of writing my own little function to split the strings. And ended up writing the one that is given below..

It might not be the ideal solution when it comes to performance but I just had to write one :)

CREATE FUNCTION [dbo].[udf_SplitSingleString] (@String1 VARCHAR(MAX) = NULL,@Seperator CHAR(1))RETURNS @TAB TABLE(DATA VARCHAR(100)
)AS
BEGIN

  SELECT @String1 = @String1 + @Seperator;

  WITH CTE AS
  (
   SELECT @String1 AS ID,SUBSTRING(@String1,1,CHARINDEX(@Seperator,@String1,1) - 1) STRI ,
   SUBSTRING(@String1,CHARINDEX(@Seperator,@String1,1)+1,LEN(@String1)) AS POS

    UNION ALL

    SELECT ID,SUBSTRING(POS,1,CASE
          WHEN CHARINDEX(@Seperator,POS,1) - 1 < 0 THEN 1
          ELSE CHARINDEX(@Seperator,POS,1) - 1
         END ),SUBSTRING(POS,CHARINDEX(@Seperator,POS,1)+1,LEN(POS))
   FROM CTE
   WHERE LEN(POS) > 1

   )
  INSERT INTO @TAB
  SELECT STRI FROM CTE
  WHERE STRI IS NOT NULL

  RETURN

ENDGO  

SELECT * FROM dbo.udf_SplitSingleString ('as$rs$asda$','$')SELECT * FROM dbo.udf_SplitSingleString ('a,s,d,f,g,',',')SELECT * FROM dbo.udf_SplitSingleString ('aasd;rwefwe;f;',';')  


Few excellent links on the same topic are - 

http://www.sqlservercentral.com/articles/Tally+Table/72993/

http://sqlperformance.com/2012/07/t-sql-queries/split-strings

http://www.codeproject.com/Tips/666620/T-SQL-Most-Practical-Split-Function