Getting the following error when running the below SELECT statement: "Invalid length parameter passed to the LEFT or SUBSTRING function." Could use a little help please. What am I missing?
SELECT (RTRIM(SUBSTRING(DESCRIPTION_1, CHARINDEX(' ',DESCRIPTION_1)+1,CHARINDEX(' ',DESCRIPTION_1,CHARINDEX(' ',DESCRIPTION_1) + 1)-CHARINDEX(' ',DESCRIPTION_1)+1-1))) AS [CCB]
FROM [Database].[Schema].[Table]
Any assistance is greatly appreciated! Thank you.
April 13, 2020 at 5:57 pm
Probably because what you are looking for doesn't have a space?
Adding NULLIF would be a good start
… DESCRIPTION_1,NULLIF(CHARINDEX...
hope this helps?
Thanks for the reply pnr8uk.
This is a piece of code from within a stored procedure. I had learned that the data it was looking for in the Description_1 column has a combination of text and numbers with multiple spaces, ie (Car 123 Wash 456). The code was written to assume there would be at least two spaces in the string and return the value between the first and second space. In this example it would return the string 123. We were getting the error as it turned out there was a value in the column that had only one space in the string.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply