title: spltting the row data with cursor and insert data into different columns
body: <p>Hi I have string like </p>
  
  <pre><code>'spla - asqlserver 2008 #P1           Q TY 1
  :SPLA-WINSV  QWSRSTDA#P29- 9 QTY2:SP
  LA-WINSVMSTDA #P29-999QTY2:SPLA-WINSVRSTD-sqlserver 2008#P 9  9 9 QT Y3:SPLA-WINSVRSTD #P 
  9-999QTY4 : SPLA-WINSVRSvr3# 59-99 Q TY5:SP -WI  Sa 1 #P    999 Q T Y6'
  </code></pre>
  
  <p>and I wrote function  like</p>
  
  <pre><code>ALTER function [dbo].[string_splitting_WORKING]
  (
   @STR VARCHAR(MAX),
   @DELIMITER CHAR(1)
  
  )
  RETURNS @Result Table
  (
  Output varchar(max)
  )
  as 
  begin
  DECLARE @Index int
  declare @Output varchar(max)
  select @index = 1
  IF @STR IS NULL RETURN
  WHILE @Index ! = 0
     BEGIN
        SELECT @Index = CHARINDEX(@Delimiter, @Str)
          IF @Index != 0
              SELECT @Output = LEFT(@Str, @Index - 1)
              --ltrim(ltrim(substring(@Str,0,charindex('#',@Str)))) 
              --LEFT(@Str, @Index - 1)
          ELSE
              SELECT @Output = @Str
          INSERT INTO @Result(Output) VALUES (@Output)
          SELECT @Str = RIGHT(@Str, LEN(@Str) - @Index)
          IF LEN(@Str) = 0 
          BREAK
      END
      RETURN
  END;
  </code></pre>
  
  <p>when u pass the string into the function like this</p>
  
  <pre><code>select * from [dbo].[string_splitting_WORKING]('SAQa # P 1      9 s-9    9 9 9  1:SPLA-WINSVRSTDA#P29-999QTY2
  :SPLA-WINS VMSTDA#P 29-99 9Q TY2:SPLA-WggaerINSVRSTD# P3 9-9 99 9 Q TY3:SPLA-WINSVRSTD#P49-9999QTY4:SPLA-WINSVRSvr3#P59-9999QTY5:SPLA-WINSVRSat1#P69-9999QTY6 :',':')
  </code></pre>
  
  <p>I am getting output like this</p>
  
  <pre><code>SAQa # P 1      9 s-9    9 9 9  1
  SPLA-WINSVRSTDA#P29-999QTY2  
  SPLA-WINS VMSTDA#P 29-99 9Q TY2
  SPLA-WggaerINSVRSTD# P3 9-9 99 9 Q TY3
  SPLA-WINSVRSTD#P49-9999QTY4
  SPLA-WINSVRSvr3#P59-9999QTY5
  SPLA-WINSVRSat1#P69-9999QTY6 
  </code></pre>
  
  <p>I wrote another function </p>
  
  <pre><code>ALTER FUNCTION [dbo].[RowStringSplit] 
  ( 
          @RowData varchar(8000), 
          @Delimiter varchar(5) 
  )   
  RETURNS @RtnValue table  
  (      ServerName varchar(1000),
          PoNumber varchar(500),
          Qty varchar(100))  
  AS   
  BEGIN  
          Declare @Cnt int 
          Set @Cnt = 1 
  
          While (Charindex(@Delimiter,@RowData)&gt;0) 
  
       Begin 
                  Insert Into @RtnValue (Servername,PoNumber)--,Qty) 
  
                  Select  ServerName = ltrim(ltrim(Substring(@RowData,0,Charindex('#',@RowData)))),
                          --PoNumber   = ltrim(ltrim(substring(@RowData,charindex('#',@RowData)+1,CHARINDEX(':',@RowData)-1))) --1
                          --PoNumber   =  substring(@RowData,charindex('#',@RowData)+1,len(@RowData))                          --2
  
                          PoNumber = substring(@RowData,charindex('#',@RowData)+1,charindex('QTY',REVERSE(@RowData))+ @Cnt)--3
  
                            --PoNumber = case charindex('#',@RowData),1) when 0 then @RowData
                            --           else
                            --               substring(@RowData,1,charindex('QTY',@RowData,1)-1 
  
                            --Qty      = substring(@RowData,charindex('QTY',@RowData),Charindex(':',@RowData)-26)
  
               Set @RowData = Substring(@RowData,Charindex(@Delimiter,@RowData)+1,len(@RowData))
               Set @Cnt = @Cnt + 1 
        End 
         Insert  Into @RtnValue (ServerName)                                  
          Select Data = ltrim(rtrim(@RowData))
          Return 
  
  END
  </code></pre>
  
  <p>and I am getting output like this</p>
  
  <pre><code>     ServerName              PoNumber  Qty
  ------------------------------------------------
  spla - asqlserver 2008        P        NULL
  SPLA-WINSV  QWSRSTDA          P2       NULL
  SP  LA-WINSVMSTDA             P29      NULL
  SPLA-WINSVRSTD-sqlserver 2008 P 9      NULL
  SPLA-WINSVRSTD                P   9    NULL
  SPLA-WINSVRSvr3                59-99   NULL
  SP -WI  Sa 1                  P    99  NULL
  </code></pre>
  
  <p>but the requirement is if i remove the data from serverName column  It has to show blank and If 
  I increase the data in the serverName column It should increase but I am getting that
  but the problem is if I remove or add the data after # until qty that is from p upto qty the reset Its not comming exactly and last I need only number in the qty column</p>
  
  <p>will u help me how to get the proper output by using function or procedure to split that string </p>
  
  <p>from anand</p>