SQL Server Query - Split a string into 3 parts based on 2 different common delimiters

Question

I'm trying to build a query that can split combined product descriptions, colors, and sizes into their individual values. I have a table full of product descriptions, colors, and sizes. Some of the product descriptions contain the color and the size each delimited with a specific string. Some of the colors and sizes are contained in their own columns. Many times, both the description and the color/size columns contain both color/size values. A common product description combined with color and size would look like this: ProductDescription..-..Color--.--Size where Color is delimited by "..-.." and Size is delimited by "--.--". Sometimes color and/or size do not exist and there is no delimiter for the query to refer to, but I still want it to split the description/color or description/size, or just return the description and blank values for color/size when neither exists...

Description and Size split up just fine, but I'm having trouble with color. I get the following error:

Invalid length parameter passed to the LEFT or SUBSTRING function.

Any help would be much appreciated!

Here's what I have so far that isn't working:

Select
    Ps.ID
    ,Case
        When Ps.ColorStart <= 5 And Ps.SizeStart <= 5 Then Ps.Description
        When Ps.ColorStart <= 5 And Ps.SizeStart > 5 Then Left(Ps.Description, Ps.SizeStart - 6)
        When Ps.ColorStart > 5 Then Left(Ps.Description, Ps.ColorStart - 6)
        Else Ps.Description
    End As DescriptionWithoutColorAndSize

    ,Case
        When Ps.PColor Is Not Null And Ps.PColor <> '' Then Ps.PColor
        When Ps.ColorStart <= 5 Or Ps.Description Is Null Or Ps.Description = '' Then ''
        When Ps.SizeStart <= 5 And Ps.ColorStart > 5 Then SUBSTRING(Ps.Description, Ps.ColorStart, 299)
        When Ps.SizeStart > 5 And Ps.ColorStart > 5 Then SUBSTRING(Ps.Description, Ps.ColorStart, Ps.ColorEndIfSizeExists - Ps.ColorStart + 1)
            --The prior line is what fails
        Else ''
    End As Color


    ,Case
        When Ps.PSize Is Not Null And Ps.PSize <> '' Then Ps.PSize
        When Ps.SizeStart <= 5 Then ''
        Else SUBSTRING(Ps.Description, Ps.SizeStart, 299)
    End As Size

From
    (
    Select
        P.ID
        ,P.Description
        ,P.Color As PColor
        ,P.Size As PSize
        ,CHARINDEX('..-..',P.Description,0) + 5 As ColorStart
        ,CHARINDEX('--.--',P.Description,0) -1 As ColorEndIfSizeExists
        ,Len(P.Description) As ColorEndIfSizeDoesNotExist
        ,CHARINDEX('--.--',P.Description,0) + 5 As SizeStart

    From
        MYProductsTable P
    ) Ps

Answer 1

All of the problems were related to the following case statement:

,Case
    When Ps.PColor Is Not Null And Ps.PColor <> '' Or Ps.ColorEndIfSizeExists - Ps.ColorStart + 1 < 0 Then Ps.PColor
    When Ps.ColorStart <=5 Or Ps.Description Is Null Or Ps.Description = '' Then ''
    When Ps.SizeStart <=5 And Ps.ColorStart > 5 Then SUBSTRING(Ps.Description, Ps.ColorStart, 299)
    When Ps.SizeStart > 5 And Ps.ColorStart > 5 Then SUBSTRING(Ps.Description, Ps.ColorStart, Ps.ColorEndIfSizeExists - Ps.ColorStart + 1)

    Else ''
End As Color

I changed all instances of "<=5" to "<6", and added a new When to the statment as follows:

    ,Case
        When Ps.ColorEndIfSizeExists - Ps.ColorStart + 1 < 0 Then SUBSTRING(Ps.Description, Ps.ColorStart, 299)
        When Ps.PColor Is Not Null And Ps.PColor <> '' Then Ps.PColor
        When Ps.ColorStart <6 Or Ps.Description Is Null Or Ps.Description = '' Then ''
        When Ps.SizeStart <6 And Ps.ColorStart > 5 Then SUBSTRING(Ps.Description, Ps.ColorStart, 299)
        When Ps.SizeStart > 5 And Ps.ColorStart > 5 Then SUBSTRING(Ps.Description, Ps.ColorStart, Ps.ColorEndIfSizeExists - Ps.ColorStart + 1)
        Else ''
    End As Color

And that fixed the problem. I don't know why... If anyone does, please feel free to explain! Thanks for all the input.