在SQL中从一列中分隔字符串

Question

我只需要在我的数据库中从这类行中分离出来

   V1;UPD;;;;;;;;;201204170000;201204180000;LGW;LOH;VAC;CP;;;;;;;X1;OL;;;;;;;

像这样

Status      UPD
datefrom    17/04/2012 00:00:00
dateto      18/04/2012 00:00:00
arrivAL     LGW
Departure   LOH
Code        VAC
Rank        CP
SpecF       X1
Extra       OL

我不知道怎样才能在水平方向粘贴我在垂直方向粘贴请帮助

我在Oracle中将此函数用于statu和rest

REGEXP_SUBSTR( EA.ACTIVITYINFO, '([^;]*)(;|$)', 1, 3, NULL, 1 )

Answer 1

听起来像是您最喜欢的ETL或导入工具的工作，每个工具都有处理、提取和转换为您想要的内容的步骤。

Answer 2

您可以创建一个函数来拆分字段并返回一个表。

CREATE FUNCTION dbo.fnParseList
(
    @Delimiter CHAR,
    @Text TEXT
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
    DECLARE @NextPos INT,
        @LastPos INT

    SELECT  @NextPos = CHARINDEX(@Delimiter, @Text, 1),
        @LastPos = 0

    WHILE @NextPos > 0
        BEGIN
            INSERT  @Result
                (
                    Data
                )
            SELECT  SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)

            SELECT  @LastPos = @NextPos,
                @NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
        END

    IF @NextPos <= @LastPos
        INSERT  @Result
            (
                Data
            )
        SELECT  SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)

    RETURN
END

下面是这段代码的original Source

实现此目的的一种效率较低的方法是使用while和substring函数。

Declare @Delimiter Char(1)= ';'; 
Declare @Text NVarchar(Max)= 'V1;UPD;;;;;;;;;201204170000;201204180000;LGW;LOH;VAC;CP;;;;;;;X1;OL;;;;;;;';
Declare @RowsToCount Int
  , @CurrentRow Int = 1;
Declare @FieldsToCount Int
  , @CurrentField Int
  , @PreviousField Int
  , @FieldID Int;


Create Table [#Test]
    (
      [Tid] Int Identity(1 , 1)
    , [TextToCheck] NVarchar(Max)
    );

Create Table [#Test2]
    (
      [Tid] Int
    , [FieldID] Int
    , [FieldStart] Int
    , [FieldEnd] Int
    , [TextOutput] NVarchar(Max)
    );

Insert  [#Test]
        ( [TextToCheck] )
        Select  @Text;

Select  @RowsToCount = Max([T].[Tid])
From    [#Test] [T];

While @CurrentRow <= @RowsToCount
    Begin
        Select  @CurrentField = 0;

        Select  @FieldsToCount = Len([T].[TextToCheck])
                - Len(Replace([T].[TextToCheck] , @Delimiter , ''))
        From    [#Test] [T]
        Where   [T].[Tid] = @CurrentRow;

        Select  @FieldID = 1;

        While @FieldID <= @FieldsToCount
            Begin
                Select  @PreviousField = @CurrentField;

                Select  @CurrentField = CharIndex(@Delimiter ,
                                                  [T].[TextToCheck] ,
                                                  @PreviousField + 1)
                From    [#Test] [T]
                Where   [T].[Tid] = @CurrentRow;

                Insert  [#Test2]
                        ( [Tid]
                        , [FieldID]
                        , [TextOutput]
                        , [FieldStart]
                        , [FieldEnd]
                        )
                        Select  [T].[Tid]
                              , @FieldID
                              , Replace(Substring([T].[TextToCheck] ,
                                                  @PreviousField ,
                                                  @CurrentField
                                                  - @PreviousField) , ';' , '')
                              , @PreviousField
                              , @CurrentField
                        From    [#Test] [T]
                        Where   [T].[Tid] = @CurrentRow;


                Select  @FieldID = @FieldID + 1;
                Print @PreviousField;
                Print @CurrentField;
            End;

        Select  @CurrentRow = @CurrentRow + 1;    
    End;

Select  [T].[Tid]
      , [T].[TextToCheck]
      , [T2].[FieldID]
      , [T2].[TextOutput]
From    [#Test] [T]
        Left Join [#Test2] [T2]
            On [T2].[Tid] = [T].[Tid];

Drop Table [#Test];
Drop Table [#Test2];

要在示例中使用这一点，您需要将原始数据插入到测试表中。

结果如下所示

Tid |TextToCheck                                                                |FieldID    |TextOutput
1   |V1;UPD;;;;;;;;;201204170000;201204180000;LGW;LOH;VAC;CP;;;;;;;X1;OL;;;;;;; |1          |V1
1   |V1;UPD;;;;;;;;;201204170000;201204180000;LGW;LOH;VAC;CP;;;;;;;X1;OL;;;;;;; |2          |UPD
1   |V1;UPD;;;;;;;;;201204170000;201204180000;LGW;LOH;VAC;CP;;;;;;;X1;OL;;;;;;; |11         |201204170000
1   |V1;UPD;;;;;;;;;201204170000;201204180000;LGW;LOH;VAC;CP;;;;;;;X1;OL;;;;;;; |12         |201204180000
1   |V1;UPD;;;;;;;;;201204170000;201204180000;LGW;LOH;VAC;CP;;;;;;;X1;OL;;;;;;; |13         |LGW
1   |V1;UPD;;;;;;;;;201204170000;201204180000;LGW;LOH;VAC;CP;;;;;;;X1;OL;;;;;;; |14         |LOH
1   |V1;UPD;;;;;;;;;201204170000;201204180000;LGW;LOH;VAC;CP;;;;;;;X1;OL;;;;;;; |15         |VAC
1   |V1;UPD;;;;;;;;;201204170000;201204180000;LGW;LOH;VAC;CP;;;;;;;X1;OL;;;;;;; |16         |CP

Answer 3

请尝试以下代码：

DECLARE @TABLE TABLE (Id int,Value Varchar(200))
DECLARE @Temp TABLE (Id int ,Value Varchar(200))
INSERT INTO @TABLE
(Id,[Value])
VALUES
(1,' V1;UPD;;;;;;;;;201204170000;201204180000;LGW;LOH;VAC;CP;;;;;;;X1;OL;;;;;;;')

INSERT INTO @Temp
SELECT A.ID,Split.a.value('.', 'VARCHAR(100)') AS Value FROM  
(SELECT ID,CAST ('<M>' + REPLACE(Value, ';', '</M><M>') + '</M>' AS XML) AS Value FROM @Table A) AS A 
CROSS APPLY Value.nodes ('/M') AS Split(a)

DELETE FROM @Temp WHERE [Value] =''
SELECT * FROM @Temp t