Separar cadena de caracteres con Store procedure

[Libras]

Código SQL:

/****** Object:  UserDefinedFunction [dbo].[SplitApplication]    Script Date: 10/19/2010 15:12:35 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
ALTER FUNCTION [dbo].[SplitApplication](@applicationid VARCHAR(MAX))
 
RETURNS @RtnValue TABLE 
(
Id INT IDENTITY(1,1),
DATA nvarchar(MAX)
) 
AS 
BEGIN 
DECLARE @Cnt INT, @SplitOn nvarchar(20)
--SET @SplitOn = '##Receive;'
SET @SplitOn = ';'
SET @Cnt = 1
 
While (Charindex(@SplitOn,@applicationid)>0)
BEGIN
INSERT INTO @RtnValue (DATA)
SELECT 
DATA = ltrim(rtrim(SUBSTRING(@applicationid,1,Charindex(@SplitOn,@applicationid)-(len(@SplitOn)))))
 
SET @applicationid = SUBSTRING(@applicationid,Charindex(@SplitOn,@applicationid)+(len(@SplitOn)),len(@applicationid))
SET @Cnt = @Cnt + 1
END
 
INSERT INTO @RtnValue (DATA)
SELECT DATA = ltrim(rtrim(@applicationid))
 
RETURN
END
 
GO
 
 
how TO USE :
 
 
SELECT 
CASE WHEN charindex('##',DATA)>=1 THEN SUBSTRING([DATA],1,charindex('##',DATA)-1) ELSE DATA END AS dato FROM
SplitApplication('[email protected]##Receive;vlizarralde@argos .com.co##Receive;[email protected]##Receive;ja [email protected]##Receive;[email protected]##Receive;[email protected]##Receive Deliver;[email protected]##Receive, Deliver;[email protected]')
 

Resultado final:

[email protected]
vlizarralde@argos .com.co
[email protected]
ja [email protected]
[email protected]
[email protected]
[email protected]
[email protected]


saludos!