Invalid length parameter passed to the LEFT or SUBSTRING function

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

Something else you can use is isnull  isnull  SUBSTRING PostCode  1   CHARINDEX      PostCode   -1   PostCode

User · Answer

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

[sql-server-2008] Invalid length parameter passed to the LEFT or SUBSTRING function

Examples related to sql-server-2008