Menu
I've seen a few of these questions asked but haven't spotted one that's helped!! I'm trying to select the first part of a postcode only, essentially ignoring anything after the space. the code i am using is
SUBSTRING(PostCode, 1 , CHARINDEX(' ', PostCode ) -1) However, I am getting Invalid length parameter passed to the LEFT or SUBSTRING function! There's no nulls or blanks but there are some the only have the first part. Is this what causing the error and if so what's the work around?
886
Solution / Work Around: This error message can easily be avoided by making sure that the integer value passed as the length to either the LEFT substring function or SUBSTRING string function is not negative. One way of checking it within the LEFT or SUBSTRING function is with the use of the CASE function.
To avoid this error, always make sure that you pass a non-negative value to the length parameter of the SUBSTRING, LEFT and RIGHT functions. If used in conjunction with the CHARINDEX function, you can use the NULLIF function together with the ISNULL function to check if the character separator is found.
SUBSTRING() is a text function that allows you to extract characters from a string. Its syntax is. SUBSTRING (expression, start, length) For the expression argument, you write a string literal or specify a column from which you want to extract the substring.
The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string.
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
select SUBSTRING(PostCode, 1 , case when CHARINDEX(' ', PostCode ) = 0 then LEN(PostCode) else CHARINDEX(' ', PostCode) -1 end) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
