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
Need help to build logic in Transformer / Server Routine
Moderators: chulett, rschirm, roy
Need help to build logic in Transformer / Server Routine
Rinky Agarwal
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
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.
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.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Code: Select all
left(string, index(field(trim(convert(convert('1234567890','',string),' ',string)),' ',1),string)+len(field(trim(convert(convert('1234567890','',string),' ',string)),' ',1)))
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
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,
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: