如何将逗号、分号和空格分隔的字符串拆分成单独的字段？

Question

我有以下3行的字段：

Field
-------
Apple, 08/01/17 - 08/30/17; Oranges, 09/01/17 - 09/30/17
Pears, 08/01/17 - Open
Grapes, 09/01/17 - 01/30/18; Oranges, 10/02/17 - 10/03/17; Banana, 11/01/17 - 12/30/17

我需要输出如下所示：

Fruit_1    Start_Date_1    Stop_Date_1    Fruit_2    Start_Date_2    Stop_Date_2    Fruit_3    Start_Date_4    Stop_Date_5
---------------------------------------------------------------------------------------------------------------------------
Apple        08/01/17        08/30/17     Oranges      09/01/17       09/30/17
Pears        08/01/17        Open
Grapes       09/01/17        01/30/18     Oranges      10/02/17       10/03/17      Banana       11/01/17        12/30/17

我有以下代码：

SELECT left(@field, charindex(',', @field) - 1) as Fruit_1, 
       substring(@field, charindex(',', @field) + 2, 8) as Start_Date_1, 
       substring(@field, charindex('-', @field) + 2, 8) as Stop_Date_3, 
       substring(@field, charindex(';', @field) + 2, charindex(',', @field, charindex(',', @field) + 1) - charindex(';', @field) - 2) as Fruit_2, 
       substring(@field, charindex(',', @field, charindex(',', @field) + 1) + 2, 8) as Start_Date_2 , 
       substring(@field, charindex('-', @field, charindex('-', @field) + 1) + 2, 8) as Stop_Date_3,
       substring(@field, charindex(';', @field) + 2, charindex(',', @field, charindex(',', @field) + 1) - charindex(';', @field) - 2) as Fruit_3, 
       substring(@field, charindex(',', @field, charindex(',', @field) + 1) + 2, 8) as Start_Date_3, 
       substring(@field, charindex('-', @field, charindex('-', @field) + 1) + 2, 8) as Stop_Date_3

但带有错误消息：

Invalid length parameter passed to the LEFT or SUBSTRING function

当尝试在苹果之后的第一行中子字符串“Orange”时，会出现此错误。请帮帮我！我正在使用Sql-server-2012

Answer 1

您应该处理在解析过程中字符串中没有结果的情况。以下是使用parsename的一种方法。它假设您的日期值具有8个符号的固定长度。

declare @t table (
    field varchar(500)
)

insert into @t
values 
    ('Apple, 08/01/17 - 08/30/17; Oranges, 09/01/17 - 09/30/17')
    ,('Pears, 08/01/17 - Open')
    ,('Grapes, 09/01/17 - 01/30/18; Oranges, 10/02/17 - 10/03/17; Banana, 11/01/17 - 12/30/17')

select
    Fruit_1 = left(p1, charindex(',', p1) - 1)
    , Start_Date_1 = substring(p1, charindex(',', p1) + 2, 8)
    , Stop_Date_1 = substring(p1, charindex('-', p1) + 2, 8)

    , Fruit_2 = iif(charindex(',', p2) = 0, '', left(p2, charindex(',', p2) - 1))
    , Start_Date_2 = iif(charindex(',', p2) = 0, '', substring(p2, charindex(',', p2) + 2, 8))
    , Stop_Date_2 = iif(charindex(',', p2) = 0, '', substring(p2, charindex('-', p2) + 2, 8))

    , Fruit_3 = iif(charindex(',', p3) = 0, '', left(p3, charindex(',', p3) - 1))
    , Start_Date_3 = iif(charindex(',', p3) = 0, '', substring(p3, charindex(',', p3) + 2, 8))
    , Stop_Date_3 = iif(charindex(',', p3) = 0, '', substring(p3, charindex('-', p3) + 2, 8))
from
    @t
    cross apply (select field2 = replace(field, ';', '.')) q1
    cross apply (select field3 = field2 + replicate('. ', 2 - len(field2) + len(replace(field2, '.', '')))) q2
    cross apply (select p1 = parsename(field3, 3), p2 = parsename(field3, 2), p3 = parsename(field3, 1)) q3

输出

Fruit_1   Start_Date_1   Stop_Date_1   Fruit_2   Start_Date_2   Stop_Date_2   Fruit_3   Start_Date_3   Stop_Date_3
--------------------------------------------------------------------------------------------------------------------
Apple     08/01/17       08/30/17      Oranges   09/01/17       09/30/17            
Pears     08/01/17       Open                       
Grapes    09/01/17       01/30/18      Oranges   10/02/17       10/03/17     Banana     11/01/17       12/30/17