SQL Server query to remove the last word from a string

Question

There is already an answer to this question in SO with the MySQL tag. Therefore, I just decided to make your life easier and put the answer below for SQL Server users. Always happy to see different answers, perhaps with better performance.

Happy coding!

Answer 1

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

Edit: make sure @YourString is cropped first, as Alex M pointed out:

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

Answer 2

 DECLARE @Sentence VARCHAR(MAX) = 'Hi This is Pavan Kumar' SELECT SUBSTRING(@Sentence, 1, CHARINDEX(' ', @Sentence) - 1) AS [First Word], REVERSE(SUBSTRING(REVERSE(@Sentence), 1, CHARINDEX(' ', REVERSE(@Sentence)) - 1)) AS [Last Word] 

Answer 3

Just adding answers.

The doc function for LEN in MSSQL:

LEN excludes trailing spaces. If this is a problem, consider using the DATALENGTH (Transact-SQL) function, which does not truncate the row. If you process a Unicode string, DATALENGTH will return twice as many characters.

The problem with the answers here is that trailing spaces are not taken into account.

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

As an example, we can cite several inputs for the accepted answer (above for reference), which will lead to incorrect results:

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

To take into account the above cases, you would need to trim the line (will return the last word of 2 or more words in the phrase):

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

The reverse side is trimmed on both sides, that is, it takes into account both leading and trailing spaces.

Or, conversely, just crop the input itself.

Answer 4

 DECLARE @String VARCHAR(MAX) = 'One two three four' SELECT LEFT(@String,LEN(@String)-CHARINDEX(' ', REVERSE(@String),0)+1)