SQL Server query to remove the last word from a string

Just an addition to answers.

The doc for LEN function in MSSQL:

LEN excludes trailing blanks. If that is a problem, consider using the DATALENGTH (Transact-SQL) function which does not trim the string. If processing a unicode string, DATALENGTH will return twice the number of characters.

The problem with the answers here is that trailing spaces are not accounted for.

SELECT SUBSTRING(@YourString, 1, LEN(@YourString) - CHARINDEX(' ', REVERSE(@YourString)))

As an example few inputs for the accepted answer (above for reference), which would have wrong results:

   INPUT        ->   RESULT
   'abcd  '     ->   'abc'        --last symbol removed
   'abcd 123 '  ->   'abcd 12'    --only removed only last character

To account for the above cases one would need to trim the string (would return the last word out of 2 or more words in the phrase):

SELECT SUBSTRING(RTRIM(@YourString), 1, LEN(@YourString) - CHARINDEX(' ', REVERSE(RTRIM(LTRIM(@YourString)))))

The reverse is trimmed on both sides, that is to account for the leading as well as trailing spaces.

Or alternatively, just trim the input itself.

SELECT SUBSTRING(@YourString, 1, LEN(@YourString) - CHARINDEX(' ', REVERSE(@YourString)))

Edit: Make sure @YourString is trimmed first as Alex M has pointed out:

SET @YourString = LTRIM(RTRIM(@YourString))