[sql-server-2008] 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?

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!

Something else you can use is isnull:

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