Search code examples
sqlsql-servert-sqlsql-server-2016

Extract all BUT the last word

I've got the following statement that successfully extracts the Last Word within a Field.

select right(rtrim([FIELDNAME]),charindex(' ',reverse(rtrim([FIELDNAME]))+' ')-1)

However, does anyone know you can extract everything up to the last word?

For example:

Joe Michael Bloggs would turn into "Bloggs" and "Joe Michael"

Solution

  • You can use a CROSS APPLY to calculate the position ONCE.

    Example

    Declare @YourTable Table ([SomeCol] varchar(50))  Insert Into @YourTable Values 
 ('Joe Michael Bloggs')
,('Mary Smith')
,('Cher')
 
Select SomeCol
      ,LastWord = right(SomeCol,Pos)
      ,TheRest  = rtrim(left(SomeCol,len(SomeCol)-Pos))
 From  @YourTable A
 Cross Apply ( values (charindex(' ',reverse(SomeCol)+' ')-1) ) B(Pos)

    Results

    SomeCol             LastWord    TheRest
Joe Michael Bloggs  Bloggs      Joe Michael
Mary Smith          Smith       Mary
Cher                Cher