Extracting String

ray.wurlod · Post by **ray.wurlod** » Thu Nov 09, 2006 9:04 am

Beware that there may be other X characters in the key. In that case a different formulation will be required, such as

If Right(InLink.TheString) = "X" Then Left(InLink.TheString,Len(InLink.TheString)-1) Else InLink.TheString

pradkumar · Post by **pradkumar** » Fri Nov 10, 2006 1:44 pm

X is also removed if it is part of some other string, which i do not want..

chulett · Post by **chulett** » Fri Nov 10, 2006 2:14 pm

Ok... if you are now saying that you only want to remove the 'X' from the field if it is the last character of the string, then you are right - you can't use a simple Convert function to do that.

You'll need to do something like Ray posted, check the last character and if it is an 'X' then extract everything up to (but not including) the last character, otherwise pass the entire field. Or count the number of 'X' characters and use EReplace to remove just the 'last' occurance. Whatever is appropriate.

pradkumar · Post by **pradkumar** » Fri Nov 10, 2006 2:27 pm

Thanks for the solution. Actually I am faced with a new problem now

My data has changed in flat file and is like:
23231A
236376A
738273A
63721A
ANGELIA
CHAMELIA
773626A

OUTPUT:
23231
236376
738273
63721
ANGELIA (HERE A SHOULD NOT BE REMOVED)
CHAMELIA (HERE A SHOULD NOT BE REMOVED)
773626

This is somewhat seems tough to me.. Any suggestions plz?

ray.wurlod · Post by **ray.wurlod** » Fri Nov 10, 2006 2:31 pm

Use Matches operator rather than equals operator.

Code: Select all

If InLink.TheString Matches "0X1N0X'A'" Then Left(InLink.TheString,Len(InLink.TheString)-1) Else InLink.TheString

or

Code: Select all

If Right(InLink.TheString) = "A" And Not(InLink.TheString Matches "1A0A") Then Left(InLink.TheString,Len(InLink.TheString)-1) Else InLink.TheString

DSguru2B · Post by **DSguru2B** » Fri Nov 10, 2006 2:31 pm

This is insane. This is like giving you a timestamp 04/05/06 and tell you to determine if its MM/DD/YY or DD/MM/YY. Next to impossible.
If you can assure that the 'A' only shows up at the end of numeric values then you can use DIGITS() transform and get done with it by testing the first value and doing a ISNUM check on it.
Some generic rules need to be layed out.

pradkumar · Post by **pradkumar** » Fri Nov 10, 2006 2:37 pm

Its not that A appears only at the end of numeric values. It also appears in others like
procter&gamble-G&A

I do not want A to be removed here.

DSguru2B · Post by **DSguru2B** » Fri Nov 10, 2006 2:39 pm

Ok i am glad there is a rule defined. Go for Ray's solution. See if it passes out all your tests. It should, Ray's solutions are pretty much all inclusive.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 10, 2006 2:40 pm

Well then work out a match pattern that will work for you. My first option would handle PROCTOR & GAMBLE - G&A properly, as there's no numeric character (to match the "1N" part of the pattern). Adapt.

pradkumar · Post by **pradkumar** » Fri Nov 10, 2006 2:43 pm

Thanks Ray.
Can you be little bit more clear Ray..

pradkumar · Post by **pradkumar** » Fri Nov 10, 2006 2:43 pm

What is this Premium Contest

chulett · Post by **chulett** » Fri Nov 10, 2006 2:53 pm

Here is a link which explains how you can enter the contest.

pradkumar · Post by **pradkumar** » Fri Nov 10, 2006 2:56 pm

I got it..How to see the full links of the solutions

ray.wurlod · Post by **ray.wurlod** » Fri Nov 10, 2006 4:22 pm

Sign up. It's less than $1 per week for the last lucky few (see home page).