Expression to get the last word of the fields

Hi,

How do i get the last word of a field in an expression?

Thanks

MosheDeutsch wrote:

Hi,

How do i get the last word of a field in an expression?

Thanks

Its a string manipulation problem right? There are lots of string functions available to you in the expression language. Check them out in the top right hand corner of the expression editor.

-Jamie

|||

I am aware of all the string functions available in the expression editor, how would I last word of a field using the functions available?

Thanks

|||Well, with Perl this would be easy!

But for your issue, what defines a "word"? How many words are there in the field? Is the number of words in a field consistent across all rows? What if the field only has one "word"? Etc... Show us some data.|||

Its not consistent across all rows, a word is delimited by a space, will always have more then one word, I am sure its possible to do it with code but I would like to now how I can do it with the expression builder

Thanks

|||You'd probably be better off trying this in a script component.|||

I was trying to eliminate script, i am not so familiar with script, but it that’s the only way I will need a sample please

Thanks

|||Add a script component to your dataflow... Add an Output Column, LastWord, of type string. Make sure that the field you are working on is selected as an input to the script component.

Then, here's your script... Note the "your-field-here" location, which represents the input field name that you are trying to get the last word of.

Imports System
Imports System.Data
Imports System.Math
Imports System.Text.RegularExpressions
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim WordArray As Array
Dim RegExprObj As Regex
Dim ArrayLength As Integer

RegExprObj = New Regex(" ")
WordArray = RegExprObj.Split(row.your-field-here)
ArrayLength = WordArray.Length
Row.LastWord = WordArray.GetValue(ArrayLength - 1).ToString
End Sub

End Class|||As an aside, I didn't know how to do this prior to you asking your question. Using Google was a big help, by the way. Search for "vb.net regular expressions" and go from there.

I don't guarantee that this is the best way to solve your problem, but it works with my test data.

You might also want to be sure that your field is trimmed before coming into this script component.|||

Thanks so much, I will try it

|||

MosheDeutsch wrote:

I am aware of all the string functions available in the expression editor, how would I last word of a field using the functions available?

Thanks

Have you actually attempted this or do you just want someone else to do your work for you?

A combination of FINDSTRING, SUBSTRING and REVERSE should be able to do it for you.

This worked for me (where [Column] is the name of a column with a sentance in it):

REVERSE(SUBSTRING(REVERSE([Column]),1,FINDSTRING(REVERSE([Column])," ",1)))

I'll stick a demo package up on my blog as well. Look in about an hour.

-Jamie

|||

Jamie Thomson wrote:

Have you actually attempted this or do you just want someone else to do your work for you?

A combination of FINDSTRING, SUBSTRING and REVERSE should be able to do it for you.

This worked for me (where [Column] is the name of a column with a sentance in it):

REVERSE(SUBSTRING(REVERSE([Column]),1,FINDSTRING(REVERSE([Column])," ",1)))

I'll stick a demo package up on my blog as well. Look in about an hour.

-Jamie

More than one way to solve the problem... Let your testing be the judge of what's correct for you.|||

Yes I did try different ways to do it with a combination of FINDSTRING, SUBSTRING and LEN I never used the REVERSE now I will for sure know the different ways how to use it and how powerful it is.

Thanks for every one

|||

Phil Brammer wrote:

More than one way to solve the problem... Let your testing be the judge of what's correct for you.

Amen to that.

There's usually more than one way to solve a problem in SSIS.

-Jamie

|||

Here's the demo:

Extract last word from a sentance
(http://blogs.conchango.com/jamiethomson/archive/2006/11/22/SSIS_3A00_-Extract-last-word-from-a-sentance.aspx)

-Jamie