Substring function when find a dash

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
sam334
Premium Member
Premium Member
Posts: 145
Joined: Mon Aug 26, 2013 7:42 pm

Substring function when find a dash

Post by sam334 »

All,
Have an issue with the substrng function in server job.

I three columns coming from several DB2 inputs. All having date, description and count. Like,
Date Count Customer_cd
11-10-2014 125 3- Marketing client

I need to have only 3 instead of 3- Marketing client. The code may change so cant use substr(1,1) rather need to find DASH and substring there.

Any help how to achieve this.

Thanks.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Use Index() to find the correct dash and then use the position it returns in your substring.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

One solution

Code: Select all

Oconv(Field(InLink.Customer_cd, " ", 1, 1), "MCN")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
sam334
Premium Member
Premium Member
Posts: 145
Joined: Mon Aug 26, 2013 7:42 pm

Post by sam334 »

Thanks Ray and Craig.. Appreciate your help. I tried both and both worked.
sam334
Premium Member
Premium Member
Posts: 145
Joined: Mon Aug 26, 2013 7:42 pm

Post by sam334 »

Just found one more thing, for only 3 - Marketing client or 4 - Human resource , both the codes were working fine.

Now we got different codes as well such as BA 100 - Outgoing call, CC 203 - Call center.

In this case, it is only picking up , BA and CC instead of BA 100.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Show us your input string, the derivation you are using, the output you are getting and what you want it to be.
-craig

"You can never have too many knives" -- Logan Nine Fingers
sam334
Premium Member
Premium Member
Posts: 145
Joined: Mon Aug 26, 2013 7:42 pm

Post by sam334 »

Craig,
Found the issue, Both index(),position and Ray's code were working fine. Issue was, there was an EM dash and dash between the description. Replaced the em dash with single dash and now it worked.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

8)
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply