Separar cadena de caracteres con Store procedure

[juanes033]

Iniciado por 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][email protected][/email]
vlizarralde@argos .com.co
[email][email protected][/email]
ja [email][email protected][/email]
[email][email protected][/email]
[email][email protected][/email]
[email][email protected][/email]
[email][email protected][/email]


saludos!

Muchisimas gracias Libras, ya me voy a acondicionar la funcion para que funcione de la tabla de donde sale la informacion, si tengo algun problema te estare molestando, ya que como te dije anteriormente soy nuevo en este mundo, pero con toda la disposicion del mundo para aprender