Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
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!
                @fjuan - it is not uncommon to use len(field)-1. In some cases len(field) is 0 and then will be -1 causing an error in some function like Left() or Substring()
– Kairan
                Mar 19, 2018 at 4:42
                @Kairan this avoids an error but how does it help return the correct results? In the event the postcode contains no space converting minus one to positive one means that it will just return the first character if used as SELECT SUBSTRING(PostCode,1,ABS(CHARINDEX(' ', PostCode ) -1)) (I'd imagine the desired correct results would be either return the entire original string or an empty string but not that)
– Martin Smith
                Jun 9, 2020 at 2:52
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.