DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
rinky0707
Participant



Joined: 28 Jul 2014
Posts: 13

Points: 181

Post Posted: Thu Dec 07, 2017 12:33 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54072
Location: Sydney, Australia
Points: 293282

Post Posted: Thu Dec 07, 2017 4:02 am Reply with quote    Back to top    

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 perhap ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
rinky0707
Participant



Joined: 28 Jul 2014
Posts: 13

Points: 181

Post Posted: Thu Dec 07, 2017 4:37 am Reply with quote    Back to top    

Thank you for your reply.

Thanks & Regards,

_________________
Rinky Agarwal
Rate this response:  
Not yet rated
priyadarshikunal



Group memberships:
Premium Members

Joined: 01 Mar 2007
Posts: 1735
Location: Troy, MI
Points: 9315

Post Posted: Thu Dec 07, 2017 5:57 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
priyadarshikunal



Group memberships:
Premium Members

Joined: 01 Mar 2007
Posts: 1735
Location: Troy, MI
Points: 9315

Post Posted: Thu Dec 07, 2017 6:02 am Reply with quote    Back to top    

Code:
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
Rate this response:  
rinky0707
Participant



Joined: 28 Jul 2014
Posts: 13

Points: 181

Post Posted: Thu Dec 07, 2017 9:32 am Reply with quote    Back to top    

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 Smile


Thanks & Regards,

_________________
Rinky Agarwal
Rate this response:  
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54072
Location: Sydney, Australia
Points: 293282

Post Posted: Fri Dec 08, 2017 7:46 pm Reply with quote    Back to top    

As I observed, there's no easy solution. Wink

But I appreciate the creativity used in this one.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours