Location of subtring in a string
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
Location of subtring in a string
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.
I tried playing with some functions in datastage transformer but nothing helped.
Any help will be appreciated.
hi sam here
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
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 ?
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 ?
hi sam here
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.
Code: Select all
YourField[Index(String,Substring,Count(String,Substring)-1),SizeOfSubstring]
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
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
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
Code: Select all
index(DSLink3.IPADD,"_",count(DSLink3.IPADD,"_")-1)
hi sam here
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Try
without -1
Code: Select all
Len(IPADD) - (Craig's function)
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?![Wink :wink:](./images/smilies/icon_wink.gif)
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?
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse