Database Error showing 537 message Invalid length parameter passed to the LEFT or SUBSTRING

[sarbarnes]

I have created a report which I have just imported into a customer site

This is the Substring i have created to separate out data that is being shown in the Variables within the Termination Call Detail report

TCD.Variable2 as MIDandStatus,

TCD.Variable3 as BrandandPlatform ,

TCD.Variable4 as RoutingReason,

SUBSTRING(TCD.Variable2,1,CHARINDEX(':',TCD.Variable2)-1) AS MID,

SUBSTRING(TCD.Variable2,CHARINDEX(':',TCD.Variable2)+1,CHARINDEX(':',TCD.Variable2,CHARINDEX(':',TCD.Variable2)+1)-1) AS Status,

SUBSTRING(TCD.Variable3,1,CHARINDEX(':',TCD.Variable3)-1) AS Brand,

SUBSTRING(TCD.Variable3,CHARINDEX(':',TCD.Variable3)+1,CHARINDEX(':',TCD.Variable3,CHARINDEX(':',TCD.Variable3)+1)-1) AS Platform,

SUBSTRING(TCD.Variable4,1,CHARINDEX(':',TCD.Variable4)-1) AS RoutedTo,

SUBSTRING(TCD.Variable4,CHARINDEX(':',TCD.Variable4)+1,CHARINDEX(':',TCD.Variable4,CHARINDEX(':',TCD.Variable4)+1)-1) AS RoutedReason,

When I have now run the report in live,  I get the database error. I did not have this issue in the Lab!

Can anyone help?

Thanks Sarah

[Jameson Gagnepain · Accepted solution]

Sarah,

It sounds like one or more of your CHARINDEX calls is returning a NULL value. This means at least one of the rows you are reporting on does not have ':' in Variable2, 3, or 4. It would be good a good idea to wrap all of your CHARINDEX calls with ISNULL. Here's the Microsoft doc on it -> ISNULL (Transact-SQL).

[sarbarnes]

Thanks Jameson, I will be able to get on the customers system tomorrow so will look to try what you have suggested,

Sarah