用游标分割行数据并将数据插入到不同的列中

Question

嗨，我有像这样的弦

'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'

我写了这样的函数

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;

将字符串传递给函数时，如下所示

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 :',':')

我得到的输出如下

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 

我写了另一个函数

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)>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

我得到了这样的输出

     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

但要求是，如果我从serverName列中删除数据，它必须显示为空，如果我增加serverName列中的数据，它应该会增加，但我得到了这一点，但问题是，如果我删除或添加# until qty之后的数据，即从p到qty它的重置不完全相同，我只需要在qty列中添加数字

您能帮助我如何通过使用函数或过程来拆分该字符串来获得正确的输出吗

来自anand

Answer 1

请试一下这个。看起来它返回了输入字符串的正确输出。

CREATE FUNCTION test (@a VARCHAR(1000))
RETURNS @RtnValue table  (
    ServerName varchar(1000),
    PoNumber varchar(500),
    Qty varchar(100)
)
AS 
BEGIN

    DECLARE @part1 VARCHAR(1000)
    DECLARE @part2 VARCHAR(1000)
    DECLARE @part3 VARCHAR(1000)
    DECLARE @i INT


    SELECT @a = LTRIM(RTRIM(@a))
    SELECT @i = PATINDEX('%#%', @a)
    IF @i = 0 SELECT @i = LEN(@a)
    SELECT @part1 = LEFT(@a, @i - 1)

    SELECT @a = RIGHT(@a, LEN(@a) - @i)

    SELECT @a = REPLACE(@a, ' ', '')
    SELECT @i = PATINDEX('%QTY%', @a)
    IF @i > 0
    BEGIN
        SELECT @part2 = LEFT(@a, @i - 1)
        SELECT @part3 = RIGHT(@a, LEN(@a) - LEN(@part2) - 3)
    END
    ELSE 
        SELECT @part2 = @a

    INSERT @RtnValue
    SELECT @part1, @part2, @part3

    RETURN
END
GO

将其与名为string_splitting_WORKING的函数一起执行

select  t.*
from [dbo].[string_splitting_WORKING]('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'
,':')
CROSS APPLY dbo.test([Output]) t

它输出：

ServerName                     PoNumber Qty
------------------------------------------------
spla - asqlserver 2008          P1            1  
SPLA-WINSV  QWSRSTDA            P29-9         2
SP  LA-WINSVMSTDA               P29-999       2
SPLA-WINSVRSTD-sqlserver 2008   P999          3
SPLA-WINSVRSTD                  P  9-999      4
SPLA-WINSVRSvr3                 59-99         5
SP -WI  Sa 1                    P999          6