--Splits a string into parts at the ';' and then whatever was between the ';' and between commas gets split into a table
CREATE  FUNCTION fn_SplitTwoDelimB (@string varchar(8000))
RETURNS @ReturnTable TABLE
(col1 Varchar(255), col2 Varchar(255))
AS
BEGIN
DECLARE
      @value varchar(8000),
      @maxidx smallint

DECLARE
      @a table
      (idx smallint, value varchar(8000))


Insert @a (idx, value)
SELECT idx, value FROM dbo.fn_SplitOneDelim(@string,';')

SELECT @maxidx = Max(idx) FROM @a

Declare @count int,
	@col1 Varchar(255),
	@col2 Varchar(255) 
Set @count = 0

WHILE @maxidx >= 0
      BEGIN
      SELECT @value = value FROM @a WHERE idx = @maxidx
      
      INSERT INTO @ReturnTable(col1,col2) SELECT	
        col1 = Max(CASE WHEN idx = 0 THEN Convert(Varchar(255), value) END),
        col2 = Max(CASE WHEN idx = 1 THEN Convert(Varchar(255), value) END)
      FROM dbo.fn_SplitOneDelim(@value,',')
    		
      SET @maxidx = @maxidx - 1
      SET @count = @count + 1
      END
	
RETURN
END	

--Splits a string into parts at the ';' and then whatever was between the ';' and the two values around the commas gets split into a table
CREATE  FUNCTION fn_SplitTwoDelim (@string varchar(8000))
RETURNS @ReturnTable TABLE
(col0 int, col1 Varchar(255), col2 Varchar(255))
AS
BEGIN
DECLARE
      @value varchar(8000),
      @maxidx smallint

DECLARE
      @a table
      (idx smallint, value varchar(8000))


Insert @a (idx, value)
SELECT idx, value FROM dbo.fn_SplitOneDelim(@string,';')

SELECT @maxidx = Max(idx) FROM @a

Declare @count int,
	@col1 Varchar(255),
	@col2 Varchar(255) 
Set @count = 0

WHILE @maxidx >= 0
      BEGIN
      SELECT @value = value FROM @a WHERE idx = @maxidx
  
      SELECT	
        @col1 = Max(CASE WHEN idx = 0 THEN Convert(Varchar(255), value) END),
        @col2 = Max(CASE WHEN idx = 1 THEN Convert(Varchar(255), value) END)
      FROM dbo.fn_SplitOneDelim(@value,',')

      INSERT INTO @ReturnTable(col0, col1,col2) VALUES(@count,@col1,@col2)
    		
      SET @maxidx = @maxidx - 1
      SET @count = @count + 1
      END
	
RETURN
END

--Splits a string at a given deliminator
CREATE FUNCTION fn_SplitOneDelim(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
	@value varchar(8000),
	@bcontinue bit,
	@iStrike smallint,
	@iDelimlength tinyint

IF @sDelim = 'Space'
	BEGIN
	SET @sDelim = ' '
	END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
	BEGIN
	WHILE @bcontinue = 1
		BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
 
		IF CHARINDEX(@sDelim, @sText)>0
			BEGIN
			SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			
--Trim the element and its delimiter from the front of the string.
			--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
			SET @idx = @idx + 1
			SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
		
			END
		ELSE
			BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
 SET @value = @sText
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			--Exit the WHILE loop.
SET @bcontinue = 0
			END
		END
	END
ELSE
	BEGIN
	WHILE @bcontinue=1
		BEGIN
		--If the delimiter is an empty string, check for remaining text
		--instead of a delimiter. Insert the first character into the
		--retArray table. Trim the character from the front of the string.
		--Increment the index and loop.
		IF DATALENGTH(@sText)>1
			BEGIN
			SET @value = SUBSTRING(@sText,1,1)
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			SET @idx = @idx+1
			SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
			
			END
		ELSE
			BEGIN
			--One character remains.
			--Insert the character, and exit the WHILE loop.
			INSERT @retArray (idx, value)
			VALUES (@idx, @sText)
			SET @bcontinue = 0	
			END
	END

END

RETURN
END

Declare @temTable Table (colName Varchar(255), value Varchar(255)) 	
--Declare @temTable Table (idx smallint Primary Key, value Varchar(8000))

Insert @temTable(colName,value) SELECT * FROM fn_SplitTwoDelim('tel,12345;home,23451;work,34512')

--Insert @temTable(idx,value) SELECT * FROM fn_SplitOneDelim('a,b,c,d',',')
SELECT * FROM @temTable

SELECT count(*) FROM @temTable