CODEWITHSUNDEEP
sql-server
Gray Meiring
Gray Meiring

Reputation: 421

Get Last Word from String in Table

I am attempting to get the last word from table Project in SQL-Server 2017.

My code is as follows:

select 
reverse(substring(reverse(ProjectDescription),1, charindex(' ', reverse(ProjectDescription)) -1)) as LastWord
from Project

The code works if I ask isolate the table to a single line, but I need the last word for all lines in the field ProjectDescription.

When I run the code as above, I get the following error:

Invalid length parameter passed to the LEFT or SUBSTRING function.

Please can someone assist me on where I am going wrong.

Upvotes: 6

Views: 31683

Answers (7)

Dawesi
Dawesi

Reputation: 586

answer above works great for me after adding the -1 for anyone looking at this at later date: (see -1 inserted below)

UPPER(reverse(substring(reverse(ProjectDescription),1, charindex(' ', reverse(ProjectDescription))-1))

Upvotes: -1

Muhammad Abid
Muhammad Abid

Reputation: 1

remove (-1) from end then execute.

select 
reverse(substring(reverse(ProjectDescription),1, charindex(' ', reverse(ProjectDescription)))) as LastWord
from Project

Upvotes: 0

Ana Butnaru
Ana Butnaru

Reputation: 11

select Ltrim(Rtrim(Right([ProjectDescription], (CHARINDEX(' ',REVERSE([ProjectDescription])))))) from Project

Upvotes: 1

Richard Crawford
Richard Crawford

Reputation: 1

This is the simplest I have come up with:

SELECT Reverse(JSON_VALUE('["' + REPLACE(Reverse('1A.2b.33C.44D4.55E'),'.','","') + '"]','$[0]'))

Upvotes: -1

Hasan Zafari
Hasan Zafari

Reputation: 385

You can use this simple function:

create function dbo.getLastWord(@s varchar(100))
returns varchar(200)
as
begin
declare @i int
set @i=len(@s)
 while substring(@s,@i,1)<>' '
  set @i=@i-1
 return substring(@s,@i,len(@s)-@i+1)
end

And call it:

select dbo.getLastWord('Feels things getting better')

which gets: 

better

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

Late answer, posted only as guidance.

There is no need to split the string. Your error was simply because charindex() returned a zero. The simple fix is to add a "fail-safe" in the charindex() by adding a space to the string, thus ensuring a hit.

Furthermore, please pay attention to Sean's advice. I had a similar split function years ago, and was amazed at the performance gains.

Example

Declare @YourTable Table ([ID] varchar(50),[ProjectDescription] varchar(50))  Insert Into @YourTable Values 
 (1,'Some Project Item or Description') -- Multiword strubg
,(2,'OneWord    ')                      -- One word with trailing blanks
,(3,NULL)                               -- A NULL value
,(4,'')                                 -- An Empty String

Select * 
      ,LastWord = right(rtrim([ProjectDescription]),charindex(' ',reverse(rtrim([ProjectDescription]))+' ')-1)
 From  @YourTable

Returns

ID  ProjectDescription                  LastWord
1   Some Project Item or Description    Description
2   OneWord                             OneWord
3   NULL                                NULL
4

Upvotes: 10

KeithL
KeithL

Reputation: 5594

I use a combination of a table valued function to split the list into an ordered list. And then only take the the last one using rownumber.

Here is a function. there are lot of these out there.

create FUNCTION SplitString
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
       ct int
      ,Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT,@ct int =0

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)
            set @ct=@ct+1

            INSERT INTO @Output(Ct,Item)
            SELECT @ct,SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

This is set up so I have a table to use. You would sub this out for your table.

declare @t as table(sentence varchar(max))

insert into @t
values
('The brown dog jumped over the fence')
,('This is the final word')

Here's the SQL to extract the last word:

select *
from (
        select * ,rn = ROW_NUMBER() over (partition by sentence order by ct desc)
        from @t t
            cross apply dbo.splitstring(t.sentence,' ')
    ) a
where rn=1

Results:

sentence                            ct  Item    rn
The brown dog jumped over the fence 7   fence   1
This is the final word              5   word    1

Upvotes: 1

Related Questions