splitting the field values

[dnat]

Hi
I want to capture the value in a field after the last space.
It is a name field and i have to capture the last word after the last space. Since the spaces are dynamic..i.e every record has a different name, i am not sure , how this can be captured.

For example, the the billing name is Jo Smith Jones, then this field should contain Jones. If the billing name is Jo Smith-Jones, then this field should contain Smith-Jones. If the billing name is Jo S Jones, then this field should contain Jones.

I cannot use the field function..Can anyone help

[Mike]

Why can't you use the Field function?

Using Field is the easiest way...

Code: Select all

Field(inLink.Name," ", Dcount(inLink.Name," "),1)

Mike

[dnat]

Thanks Mike, definitely i can use this. But i want to calculate it from the backward. so, i think i should store the delimiter count in a variable and then use the variable in the field function.
i think whatever you have given will give me the value after the first space. but i want to take the value after the last space(which would have to be calculated dynamically based on the space count)
am i right?

[Sainath.Srinivasan]

Dnat,

Did you read what Mike suggested? It will work for you.

[dnat]

i understood..thanks a lot.

[ray.wurlod]

This is one I use at nearly every engagement. So I took the trouble to create a function. It's a server routine (I use it mainly in Routine activities from sequences) but should be able to be adapted readily as a parallel routine.

First, the long description:

Input Arguments
SourceString - String from which the rightmost delimited substring is to be extracted.
Delimiter - Single character that delimits substrings in SourceString.
Returns the rightmost delimited substring of SourceString.

If Delimiter is empty ("") returns the rightmost character of SourceString.
If Delimiter contains more than one character, the first character is used.
If either argument is null or unassigned, returns null.

Code: Select all

FUNCTION FinalDelimitedSubstring(argSourceString, argDelimiter)
$COPYRIGHT "Copyright (c) 2002-2005 Ray Wurlod.  All rights reserved."
$* May be freely used with the above copyright notice intact and the code unmodified.


      Equate RoutineName To "FinalDelimitedSubstring"

$DEFINE REPORTNULLARG

      * Ensure both arguments have values
      If UnAssigned(argSourceString) Or IsNull(argSourceString) Or UnAssigned(argDelimiter) Or IsNull(argDelimiter)
      Then

         Ans = @NULL
$IFDEF REPORTNULLARG
         Call DSLogWarn("Unassigned or null argument.", RoutineName)
$ENDIF

      End
      Else

         * If delimiter is empty, return rightmost character.
         If argDelimiter = ""
         Then

            Ans = Right(argSourceString, 1)

         End
         Else

            * If delimiter contains more than one character, only the first character is used.
            Delimiter = Left(argDelimiter, 1)

            FinalField = DCount(argSourceString, Delimiter)
            Ans = Field(argSourceString, Delimiter, FinalField, 1)

         End

RETURN(Ans)