CODEWITHSUNDEEP
sqlsql-server
Stephan Rieber
Stephan Rieber

Reputation: 35

Need to retrieve 2nd to last word in SQL

I am trying to retrieve the 2nd to last word from a Column Value.

I have used both

[desc],
REVERSE( LEFT( REVERSE([Desc]), CHARINDEX(' ', REVERSE([Desc]))-1 ) ) [Last Name],

(
ltrim(substring([desc],charindex(' ',[desc]), CHARINDEX(' ',ltrim(SUBSTRING([desc],charindex(' ',[desc]),LEN([desc])-charindex(' ',REVERSE( left( REVERSE([Desc]), CHARINDEX(' ', REVERSE([Desc]))-1 ) ))))) ))
) [First Name]

but I am just getting the 2nd word from the front of the sentence and the last word of the sentence. My brain is just not computing what I need to do. The last 2 words from the string is the First and Last name.

Upvotes: 0

Views: 724

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I think I would just look up a SplitString() function on the web and use that:

select t.*
from (select t.*, row_number() over (partition by t.id order by ind desc) as seqnum
      from table t outer apply
           splitstring(t.[desc])(str, ind)
     ) t
where seqnum = 2;

Upvotes: 2

Related Questions