Need help to build logic in Transformer / Server Routine

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
rinky0707
Participant
Posts: 13
Joined: Mon Jul 28, 2014 4:53 am

Need help to build logic in Transformer / Server Routine

Post by rinky0707 »

Hello Everyone,

I have a requirement of fetching a substring from a string as mentioned below-

If String is ABC101XYZ ; I need subtring ABC101 from this.
I need substring after truncating everything that comes after the digits.
The input string is of variable length and it is not necessary that first 3 characters will be alphabetic.
Some more examples of string and substring are given below-

ABCXYZ -- ABCXYZ
AB101-XYX-101-XYZ -- ABC101.
A10-A10 -- A10.

Please see if you can help me with the logic to accomplish the above.
I need this for one of my fields in Transformer.


Thanks & Regards
Rinky Agarwal
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

There's no easy solution using just a Transformer stage (unless you call a custom routine from it).

You might like to use an External Filter stage with an appropriate filter (sed or awk or perhaps even grep).

In a BASIC Transformer stage you could use the MatchField() function to extract the characters preceding the numeric characters and another the numeric characters themselves, and then concatenate those two.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
rinky0707
Participant
Posts: 13
Joined: Mon Jul 28, 2014 4:53 am

Post by rinky0707 »

Thank you for your reply.

Thanks & Regards,
Rinky Agarwal
priyadarshikunal
Premium Member
Premium Member
Posts: 1735
Joined: Thu Mar 01, 2007 5:44 am
Location: Troy, MI

Post by priyadarshikunal »

1. replace all non numeric characters to space (you can do double convert),
2. do a default trim to remove leading, trailing, repeating spaces.
3. use field function to get the first set of digits with delimiter as space.
4. take its index and add the length of first set of digits and you have the length of string to use in left command
5. use left(string, length in previous step)

you can nest all these things in one line or use stage variables to simplify the derivation.
Priyadarshi Kunal

Genius may have its limitations, but stupidity is not thus handicapped. :wink:
priyadarshikunal
Premium Member
Premium Member
Posts: 1735
Joined: Thu Mar 01, 2007 5:44 am
Location: Troy, MI

Post by priyadarshikunal »

Code: Select all

left(string, index(field(trim(convert(convert('1234567890','',string),' ',string)),' ',1),string)+len(field(trim(convert(convert('1234567890','',string),' ',string)),' ',1)))
been long to remember the syntax but the code should look like above
Priyadarshi Kunal

Genius may have its limitations, but stupidity is not thus handicapped. :wink:
rinky0707
Participant
Posts: 13
Joined: Mon Jul 28, 2014 4:53 am

Post by rinky0707 »

Hey Priyadarshi, Thanks a lot. It just worked perfectly.
After a little modification in the syntax the below gave the expected substring.

left(DSLink42.Col1,Index(DSLink42.Col1,field(trim(convert(convert('1234567890','',DSLink42.Col1),' ',DSLink42.Col1)),' ',1),1)+len(field(trim(convert(convert('1234567890','', DSLink42.Col1),' ',DSLink42.Col1)),' ',1))-1)

Many Thanks :-)


Thanks & Regards,
Rinky Agarwal
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

As I observed, there's no easy solution. ;)

But I appreciate the creativity used in this one.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply