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
