DSXchange

I want to get the location of a substring in a string in datastage just like INSTR() in sql.

I tried playing with some functions in datastage transformer but nothing helped.

Any help will be appreciated.

Did you try with INDEX function ?

I just tried INDEX and it worked good.

In one of my scenario I want to read the data from right side and get the location of the substring. I tried with Right() but it dint work.

Right() performs a substring starting from the right-hand side, so really wouldn't help you. You'd need to find all occurances of the substring and then take the last / highest position one, I guess.

Yes Craig I just need the second occurance location of the substring but counting from right side, currently its giving me the location counting from left hand side.

Is there any way i can make index read the string from right hand side and get me the location of the second occurance of the substring ?

AFAIK, you can't count anything from the right-hand side so you'd need to check for the total number of occurances and take the next to last one. Perhaps a combination of Count() to tell you how many times the substring occurs in the string and then Index() to tell you where to start substringing from? As long as you can use Count() in PX then something like this should work:
Off the top of my head. Best to do these kind of things step-wise in a routine so you can make it more robust, error-wise, but you could inline everything if you knew there would always be at least two occurances to find.

Count the total occurance and take one less using index.

Count the total occurance and take one less using index.

Which is exactly what I posted, example code and all.

This clearly proves that "thinking alike" is not restricted to wise people.

Thanks Craig and sainath for your suggestions.

Let me elaborate my requirement

If my data contains the value "ABC 12345_10_19" then I want the location of second '_' from right hand side which will be 6.

When I use the code provided by craig it is givinig me the occurance of second '_' from right hand side but still index is counting from left so it is giving me 10.

Let me know if I am doing anything wrong.... my code was

Try
without -1

Since you substring 'from the left' you need to count from the left as well.

What's your end game, what exactly do you want to do / extract once you have this position? I've been assuming return the substring from the string but perhaps it is something else. Using your example of "ABC 12345_10_19" what exactly do you want? Just literally the "6" which is the count from the right? Or do you need to do something with it after you get the 6?

You do realize that, if you have the count "from the left" you can get the count "from the right" by utilizing the length of the string and a silly something called "subtraction", yes?

It is giving 5 now instead of 6.

Funny, but again we just made the same suggestion.

ps. Add 1.