Search code examples
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?

Solution

  • 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)