Contents
What is invalid length parameter passed to the left or substring function?
This error is caused by passing a negative value to the length parameter of the SUBSTRING, LEFT and RIGHT string functions. This usually occurs in conjunction with the CHARINDEX function wherein the character being searched for in a string is not found and 1 is subtracted from the result of the CHARINDEX function.
How does Instr work in SQL?
SQL INSTR() function detects the first occurrence of a string or a character in the other string. Thus, the INSTR() function witnesses the position of the initial/first occurrence of any string/sub-string in another string data value.
What is SQL Patindex?
Patindex() Function in SQL Server The Patindex function returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found. Patindex can use wildcard characters.
Is there a left function in SQL?
SQL Server LEFT() Function The LEFT() function extracts a number of characters from a string (starting from left).
What does invalid length parameter passed to left or substring function mean?
I’m running into the “Invalid length parameter passed to the LEFT or SUBSTRING function” error, but it goes away and the query works when I include the column I’m passing into those functions, any clue?
Is the first call to substring an invalid parameter?
Your first call to SUBSTRING specifies a length of SUBSTRING (i.ShortDescription, 25, CHARINDEX (‘-‘, i.ShortDescription, 25)). The problem is that your outer call to SUBSTRING is being passed a character data type from the inner SUBSTRING call in the third parameter.
What happens if postcode is missing a space?
That would only happen if PostCode is missing a space. You could add conditionality such that all of PostCode is retrieved should a space not be found as follows CHARINDEX will return 0 if no spaces are in the string and then you look for a substring of -1 length.