Invalid length parameter

Mark Wilson
Mark Wilson used Ask the Experts™
on
I have the following query I am using - it throws up the following error message

Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.

Can anybody help?



;WITH [WL] AS (

SELECT        [L].[LookupId],
       [L].[LookupRuleSet],
       [L].[LookupVariety], 
       [L].[LookupType],
       [L].[LookupValue],
        REPLACE([L].[LookupString], '[' + [SV].[CA] + ']', '') [LookupString],
        [SV].[C] [CaseCategory]

FROM [Meta].[Lookup] [L]
LEFT OUTER JOIN (

       SELECT [CS].[LookupId],
              [CS].[CA],
              [SPLIT].[CS].value('.', 'INT') AS [C]

       FROM (

              SELECT [L].[LookupId],
                     LEFT(RIGHT([L].[LookupString], LEN([L].[LookupString]) - PATINDEX('%[[]%', [L].[LookupString])), PATINDEX('%]%', RIGHT([L].[LookupString], LEN([L].[LookupString]) - PATINDEX('%[[]%', [L].[LookupString]))) - 1) [CA],
                     CAST('<XL>' + REPLACE(LEFT(RIGHT([L].[LookupString], LEN([L].[LookupString]) - PATINDEX('%[[]%', [L].[LookupString])), PATINDEX('%]%', RIGHT([L].[LookupString], LEN([L].[LookupString]) - PATINDEX('%[[]%', [L].[LookupString]))) - 1), ',', '</XL><XL>') + '</XL>' AS XML) AS [C] 

              FROM [Meta].[Lookup]  [L]

       ) AS [CS] CROSS APPLY [C].nodes ('/XL') AS SPLIT([CS])

) AS [SV] ON [L].[LookupId] = [SV].[LookupId]

)



UPDATE  [Motor].Matter
SET     WorkSource = CASE WHEN [WL].[LookupValue] IS NULL THEN 'Others'
                          WHEN [Matter].[System] = 'IMELEgis' THEN 'IMe'
                          ELSE [WL].[LookupValue]
                     END
FROM    [Motor].[Matter] [Matter]
        LEFT OUTER JOIN [Motor].[Client] cl ON cl.ClaimID = Matter.ClaimID
        LEFT OUTER JOIN [WL] ON [Matter].[CaseType]
                                + COALESCE([Matter].[NUScheme], '') LIKE CASE
                                                              WHEN [WL].[LookupType] IN (
                                                              'Endswith',
                                                              'Wildcard' )
                                                              THEN '%'
                                                              ELSE ''
                                                              END
                                + REPLACE([WL].[LookupString], '||', '%')
                                + CASE WHEN [WL].[LookupType] IN (
                                            'Startswith', 'Wildcard' )
                                       THEN '%'
                                       ELSE ''
                                  END
                                AND [cl].[MotorCostLienCat2] = [WL].[CaseCategory]
                                AND [WL].[LookupRuleSet] = 'Motor'
                                AND [WL].[LookupVariety] = 'Worksource'

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
It's hard to find which part of code returning the error since we don't have your data. You'll need to check if any of the PATINDEX functions isn't returning a valid number (1 or superior):
LEFT(RIGHT([L].[LookupString], LEN([L].[LookupString]) - PATINDEX('%[[]%', [L].[LookupString])), PATINDEX('%]%', RIGHT([L].[LookupString], LEN([L].[LookupString]) - PATINDEX('%[[]%', [L].[LookupString]))) - 1)
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
It seems like it must be that '[' is missing.
I'd use a CROSS APPLY to assign an alias name for the open_bracket location, then you can test for 0 and resolve that however you need to.

;WITH [WL] AS (

SELECT        [L].[LookupId],
       [L].[LookupRuleSet],
       [L].[LookupVariety],
       [L].[LookupType],
       [L].[LookupValue],
        REPLACE([L].[LookupString], '[' + [SV].[CA] + ']', '') [LookupString],
        [SV].[C] [CaseCategory]

FROM [Meta].[Lookup] [L]
LEFT OUTER JOIN (

       SELECT [CS].[LookupId],
              [CS].[CA],
              [SPLIT].[CS].value('.', 'INT') AS [C]

       FROM (

              SELECT [L].[LookupId],
                     LEFT(RIGHT([L].[LookupString], LEN([L].[LookupString]) - open_bracket),
                          PATINDEX('%]%', RIGHT([L].[LookupString], LEN([L].[LookupString]) - open_bracket)) - 1) [CA],
                     CAST('<XL>' + REPLACE(LEFT(RIGHT([L].[LookupString], LEN([L].[LookupString]) - open_bracket),
                          PATINDEX('%]%', RIGHT([L].[LookupString], LEN([L].[LookupString]) - open_bracket)) - 1),
                          ',', '</XL><XL>') + '</XL>' AS XML) AS [C]
              FROM [Meta].[Lookup]  [L]
             
             CROSS APPLY (
                     SELECT CASE WHEN PATINDEX('%[[]%', [L].[LookupString]) = 0
                                 THEN 1 --adjust depending on how you want to handle missing '['
                                 ELSE PATINDEX('%[[]%', [L].[LookupString]) END AS open_bracket
              ) AS assign_alias_names


              ) AS [CS] CROSS APPLY [C].nodes ('/XL') AS SPLIT([CS])

) AS [SV] ON [L].[LookupId] = [SV].[LookupId]

)

UPDATE  [Motor].Matter
...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial