SQL statement to extract the last word in a string?

Question

I run a SELECT statement with a CHARINDEX in SQL Server 2016 to return the last word in a string using a space as a delimiter. It worked for some fields and for others, it strips out some characters. Can someone please point me in the right direction?

I've tried the RIGHT, LEFT and REVERSE in-built functions as well as the SUBSTRING function.

SELECT TRIM(RIGHT('XXxxx Xxxxxx x Xxxxxx Xxxxx Xxxx Xxxxxx 5 - Xxxxxx - Xxxxxx Xxxxxxx Xxxxxxxxxx Xxxxxxxxxx',CHARINDEX(' ','XXxxx Xxxxxx x Xxxxxx Xxxxx Xxxx Xxxxxx 5 - Xxxxxx - Xxxxxx Xxxxxxx Xxxxxxxxxx Xxxxxxxxxx',0)+1))

I expected the last word - Xxxxxxxxxx but for this particular record, the above query returns xxxxxxx and ignores the preceding 3 characters. For some other records, it works and returns even up to 8 characters. I will be grateful if pointed in the right direction.

Answer 1

See code below. The idea is:

1. reverse the string.
2. find the first space after reversing
3. get everything to the left of space
4. reverse it again, as the string is reversed.

Rather than just give you the the straight answer, I have broken it down so you can understand it a bit better

declare @something varchar(20) = '1234 5678 192'

select @something as original
  , reverse(@something) as reversed
  , charindex(' ',reverse(@something)) last_space_position
  , left(reverse(@something), charindex(' ',reverse(@something))) what_you_need_in_reverse
  , reverse(left(reverse(@something), charindex(' ',reverse(@something)))) what_you_need