Monday, June 29, 2009

Retrieve the last word of a string

About FuncGetLastWord

/*This function accepts a string as parameter */
/*This function returns the last word of the string*/
/*This function calls another Function FuncGetWord*/

CREATE FUNCTION dbo.FuncGetLastWord(@varString as varChar(2000)) RETURNS varChar(2000) AS



BEGIN

Declare @varLastWord as varChar(2000)
Declare @varWord as varChar(2000)
Declare @w As Integer

Set @W = 1

set @varWord = dbo.FuncGetWord(@varString,@W)
set @varLastWord = ''

If len(@varWord) = 0 Begin
Return @varLastWord
End

While(Len(@varWord)>0) Begin

set @varLastWord = @varWord
set @W = @W + 1
set @varWord = dbo.FuncGetWord(@varString,@W)

End

Return @varLastWord
End


FuncGetWord Function

CREATE FUNCTION [dbo].[FuncGetWord] (@varString as varChar(2000), @intPosition as integer) RETURNS varChar(2000) AS
BEGIN

Declare @i As Integer
Declare @j As Integer
Declare @k As Integer
Declare @varResult as varChar(2000)

Set @j = 0
Set @k = 1
Set @i = 1

Set @varString = ltrim(rtrim((@varString)) + ' ^'

While(@i<=Len(@varString)) Begin
If (Substring(@varString, @i, 1) = ' '

or Substring(@varString, @i, 1) = '.'
or Substring(@varString, @i, 1) = '&'
or Substring(@varString, @i, 1) = ','

)
and Substring(@varString, @k, @i - @k +1 ) <> ' ' Begin
Set @j = @j + 1
If @j = @intPosition and Substring(@varString, @k, @i - @k +1 ) <> '^' Begin
set @varResult = ltrim(rtrim(Substring(@varString, @k, @i - @k +1 )))
Return @varResult
End
Else Begin
Set @k = @i + 1
End
End
Set @i = @i + 1
End

If @j < @intPosition Begin
Set @varResult = Null
End
Else Begin
Set @varResult = substring(@varString, @k,2000)
End

Return @varResult

End


USAGE
dbo.FuncGetLastWord('Your String'

/* Example: dbo.FuncGetlastWord('Hello World') = World */

No comments:

Post a Comment