DSXchange

Hello to all,

can anyone help me on this issue:

I have this string: RM1246873TS98475

and I want that concatenate WS to the next alphabetic character that was found, example:

RMTS98475

But the issue is that I don't know witch characters and the position of then!

Thank you,
Elisabete

Which version of datastage you are using?

Also please provide more details on the problem. Will there be only two two character strings will be there, what needs to be done if there are more than 2 of the same. Also if there are more scenarios to be covered, provide examples.

The version of datastage is: 8.1.

There no more scenarios, only this one, and yes are only two alphabetic characters.

you can help me?

"WS" or "TS"?

If it's always two characters then an approach using substring would be the most efficient. For example:

but I don't know the position of the second alphabetic characters... this string is just a example... it can be in many ways:

RM1246873TS98475
RM12473TS98475
RM12473TS984

NOTE: I can't read premium posts!

1)use convert function to replace some dummy character say # for all the alphabets
2)use index function to find the position of third occurrence of # and then perform substring and concatenation

Yes, Vamsi.4a6 is correct

as I see the examples the first two places has the first string, you need to find the second one and then concatanate the first two with everything else after you encounter the 3rd character. In that case you can use the following derivation. Also I prefer to perform index(convert()) in a stage variable and use the output in below code.

SMALL MODIFICATION IN DERIVATION

InLink.TheString[1,2]:InLink.TheString[index(convert('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz','#',InLink.TheString),'#',3),len(InLink.TheString) - index(convert('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz','#',InLink.TheString),'#',3)+1]

Please correct me if i am wrong

ex-RM124TS984
index(convert('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz','#',InLink.TheString),'#',3) WILL GIVE 6
len(InLink.TheString)- WILL GIVE 10
index(convert('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz','#',InLink.TheString),'#',3)-WILL GIVE 6
SO InLink.TheString[6,10-6] WILL EXTRACT ONLY FOUR CHARACTERS SO WE NEED TO ADD +1 TO IT

yes, you are correct vamsi, I missed adding 1 to it.

Think Ray's point is that if you are always looking for TS then just find the index of TS. (and/or WS if that is a potential)

There's a very elegant solution if you're prepared to use the BASIC Transformer stage (or a server Transformer in a server Shared Container in your parallel job, or a server job). You can use server pattern matching via the MatchField() function, and delimited field replacement via the FieldStore() function.

Try using FIELD function with delimiter string as "TS"

Lather you can concatenate 2nd part of your string for the concatenation
Ex.
Link.Column[1,2]:FIELD[Link.Column,"TS",2]

Let me know if it works out...