Как разбить строку, разделенную запятой, точкой с запятой и пробелами, на отдельные поля?

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

Эта ошибка возникает при попытке подстроки "Апельсины" в первой строке после яблок. Пожалуйста помоги! Я использую Sql-сервер-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