Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table like this

VM1 SPLA - WINSVRSTD #P19-9999 QTY 1
VM2 SPLA - WINS ST   #P29-9 9 QTY 2 : SPLA - WINSVRENTAAA  #P39- 9 9 99 QTY 3:SPLA - WINS ST   #P29-9 9 QTY 2 : SPLA - WINSVRENTAAA  #P39- 9 9 99
VM3 SPLA - WINSVRSTD #P39-9999 QTY 4
VM4 SPLA - WINSVRSTD #P59-9999 QTY 2 : SPLA - WINSVRENT-023 #P39-9 9 99 QTY 3 

And I create the cursor for the above table with split function along with cross apply like this:

declare @Name varchar(100),@Descr varchar(max)
declare outerstgcursor cursor for
                                                --Select Name,Descr as Descr from T_Data  D                 --cross apply dbo.StringSplitting_PW (d.description,':')
select Name,Descr as Descr from T_Data D
cross apply dbo.StringSplitting_PW(d.description,':')
open outerstgcursor
fetch next from outerstgcursor into @Name,@Descr
while (@@fetch_status = 0)
begin
     fetch outerstgcursor into @Name,@Descr  -- A
       begin                                 -- A
           print @Name + ' -------- ' + @Descr -- A

                 end -- A

     end     -- A 
        close outerstgcursor      -- A
        deallocate outerstgcursor -- A 


the split function I am using here is 

USE [TRAINEE]
GO
/****** Object:  UserDefinedFunction [dbo].[StringSplitting_PW]    Script Date: 02/29/2012 22:57:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[StringSplitting_PW] (@string NVARCHAR(MAX),@separator NCHAR(1)) 
RETURNS
 @Temp TABLE (Descr NVARCHAR(MAX))


AS 
BEGIN
   DECLARE @p int 
   SET @p = 0


SET @string = (@string + @separator)
WHILE charindex(@separator,@string,@p) <> 0
      BEGIN

                                                                              -- declare @lclstg varchar(10)                             --M
                                                                              -- set @lclstg= convert(varchar(5),@cnt)                    --M

insert into @temp 



--SELECT ltrim(dbo.getstring(substring(@string,@p,charindex(@separator,@string,@p) - @p)))--,@name   --M  

                                                                                --select  substring(ltrim(dbo.getstring(substring(@string,@p,charindex(@separator,@string,@p) - @p))) ,0,4) --R
SELECT substring(@string, @p, charindex(@separator,@string,@p) - @p)            -- A

--SELECT ltrim(dbo.ZEROTOHASH(dbo.HASHTOQTY(substring(@string, @p, charindex('QTY',@string,@p) + @p))))

 SET @p = charindex(@separator,@string,@p) + 1

                                                                                --set @cnt = @cnt + 1 --M

      END
     RETURN                                                                     
END 

And I am getting the output like this:

VM2 -------- SPLA - WINSVRSTD #P29-9999 QTY 2 
VM2 --------  SPLA - WINSVRENT #P39-9999 QTY 3
VM3 -------- SPLA - WINSVRSTD #P39-9999 QTY 3
VM4 -------- SPLA - WINSVRSTD #P59-9999 QTY 2 
VM4 --------  SPLA - WINSVRENT #P39-9999 QTY 3 
VM4 --------  SPLA - WINSVRSMB #P39-9999 QTY 5
VM6 -------- SPLA - WINSVRSTD #P69-9999 QTY 6
VM5 -------- SPLA - WINSVRSTD #P59-9999 QTY 5 
VM5 --------  SPLA - SQLSERVER-08 #P59-9999 QTY 5
VM7 -------- SPLA - WINSVRSTD #P79-9999 QTY 6
VM8 -------- SPLA - WINSVRSTD #P89-9999 QTY 1
VM8 -------- SPLA - WINSVRSTD #P89-9999 QTY 1

But I need the output like this:

Name          ServerName         PoNumber        QTY
-----------------------------------------------------
VM1           SPLA - WINSVRSTD   P29-9999       2
VM2 --------  SPLA - WINSVRENT   P39-9999       3
VM3 --------  SPLA - WINSVRSTD   P39-9999       3
VM4 --------  SPLA - WINSVRSTD   P59-9999       2 
VM4 --------  SPLA - WINSVRENT   P39-9999       3 

But, the thing is if I remove or add the string from the ServerName column, the data should fit into that column, same for PoNumber column.

E.g., if I add string in ServerNamecolumn like spla-winsvrstdserver-08 it fit into that column. If I delete some string in ServerName column like spla-winsvr, it should fit into that column. Same for PoNumber, if I delete p or some number from PoNumber column it has to be fit into that column

share|improve this question

closed as too localized by JNK Sep 7 '12 at 18:47

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

3  
You should se code formatting in the questions, otherwise your output and code is illegible. –  ConcernedOfTunbridgeWells Feb 29 '12 at 17:34
    
What's the format of T_Data? Is the data in your first example stored in columns or a single wide text column? If the latter, the fix lies in in how you're importing the data. –  Jon of All Trades May 29 '12 at 23:07