DSXchange

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

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.

Thank you for your reply.

Thanks & Regards,

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.

been long to remember the syntax but the code should look like above

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,

As I observed, there's no easy solution.

But I appreciate the creativity used in this one.