Untitled

how to get the desired output by executing below function

ALTER FUNCTION [dbo].StringSplitting_23012012 (@string NVARCHAR(MAX),

@Delimiter NVARCHAR(1))

RETURNS

 @Temp TABLE (string NVARCHAR(MAX))

AS

BEGIN

   DECLARE @p int

   SET @p = 0

   --SET @string = (@string + @Delimiter)

   WHILE charindex(@Delimiter,@string) < 0 --,@p) <> 0

     BEGIN

       INSERT into @Temp

       SELECT substring(@string,@p,charindex(@Delimiter,@string)) --+ @p)

       --select   substring(@string,5,15) SET @p = charindex(@Delimiter,@string) --1

     END

   RETURN

END

       

SELECT * FROM [dbo].StringSplitting('SPLA-WINSVRSTD #P19-1999 QTY1:

SPLA-WINSVRSTD #P12-2999 QTY2 : SPLA-WINSVRSTD #P19-1399 QTY3 :

SPLA-WINSVRSTD #P19-1399 QTY4 : SPLA-WINSVRSTD #P59-5999 QTY5 :

SPLA-WINSVRSTD #P69-6999 QTY6 : SPLA-WINSVRSTD #P79-7999 QTY7 :

SPLA-WINSVRSTD #P69-6999 QTY8',':')

       

SPLA-WINSVRSTD #P19-1999 QTY1

SPLA-WINSVRSTD #P12-2999 QTY2

SPLA-WINSVRSTD #P19-1399 QTY3  

SPLA-WINSVRSTD #P19-1399 QTY4

SPLA-WINSVRSTD #P59-5999 QTY5

SPLA-WINSVRSTD #P69-6999 QTY6

SPLA-WINSVRSTD #P79-7999 QTY7

SPLA-WINSVRSTD #P69-6999 QTY8

       

SPLA P12-2999 QTY2

SPLA P19-1399 QTY3  

SPLA P19-1399 QTY4

SPLA P59-5999 QTY5

SPLA P69-6999 QTY6

SPLA P79-7999 QTY7

SPLA P69-6999 QTY8

       

SELECT * FROM [dbo].StringSplitting(

REPLACE('SPLA-WINSVRSTD #P19-1999 QTY1:

         SPLA-WINSVRSTD #P12-2999 QTY2 : SPLA-WINSVRSTD #P19-1399 QTY3 :

         SPLA-WINSVRSTD #P19-1399 QTY4 : SPLA-WINSVRSTD #P59-5999 QTY5 :

         SPLA-WINSVRSTD #P69-6999 QTY6 : SPLA-WINSVRSTD #P79-7999 QTY7 :

         SPLA-WINSVRSTD #P69-6999 QTY8', '-WINSVRSTD #','')

,':')

       

SELECT * FROM [dbo].StringSplitting(REPLACE(

'SPLA-WINSVRSTD #P19-1999 QTY1 : SPLA-WINSVRSTD #P12-2999 QTY2 : SPLA-WINSVRSTD #P19-1399 QTY3 : SPLA-WINSVRSTD #P19-1399 QTY4 : SPLA-WINSVRSTD #P59-5999 QTY5 : SPLA-WINSVRSTD #P69-6999 QTY6 : SPLA-WINSVRSTD #P79-7999 QTY7 : SPLA-WINSVRSTD #P69-6999 QTY8'

, '-WINSVRSTD #','') ,':')