Invalid length parameter passed to the LEFT or SUBSTRING function

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?

5 Answers

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) 

CHARINDEX will return 0 if no spaces are in the string and then you look for a substring of -1 length.

You can tack a trailing space on to the end of the string to ensure there is always at least one space and avoid this problem.

SELECT SUBSTRING(PostCode, 1 , CHARINDEX(' ', PostCode + ' ' ) -1) 

This is because the CHARINDEX-1 is returning a -ive value if the look-up for " " (space) is 0. The simplest solution would be to avoid '-ve' by adding

ABS(CHARINDEX(' ', PostCode ) -1))

which will return only +ive values for your length even if CHARINDEX(' ', PostCode ) -1) is a -ve value. Correct me if I'm wrong!

One of the selected column is null or empty.

Something else you can use is isnull:

isnull( SUBSTRING(PostCode, 1 , CHARINDEX(' ', PostCode ) -1), PostCode)