Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett , rschirm , roy
dsdoubt
Participant
Posts: 106 Joined: Sat Jul 15, 2006 12:17 am
Post
by dsdoubt » Tue Nov 11, 2008 12:22 pm
What is the efficient way to remove Non Numeric for a field.
Eg: I need to remove the non numeric character in the Field "No"
Input
-----
Code: Select all
ID | No
1|003A23
2|234-456
3| 2333
Output
--------
I dont think I need to specify all the non numeric character in the Convert function to get stripped out.
ray.wurlod
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Post
by ray.wurlod » Tue Nov 11, 2008 12:56 pm
I think you do, unless you have additional information which you have not shared about the likely non-numeric characters that may appear in the data.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
dsdoubt
Participant
Posts: 106 Joined: Sat Jul 15, 2006 12:17 am
Post
by dsdoubt » Tue Nov 11, 2008 3:05 pm
I was asking if I need to use Convert function.
If I need to use that, I need to specify all the list of non numeric character in the list. Which I don't want to.
ray.wurlod
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Post
by ray.wurlod » Tue Nov 11, 2008 4:23 pm
Tough.
Create a stage variable containing a list of them. Initialize it only; you don't need to re-derive for every row processed.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Mike
Premium Member
Posts: 1021 Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL
Post
by Mike » Tue Nov 11, 2008 4:41 pm
How about a double convert to avoid identifying/listing all possible non-numeric characters:
Code: Select all
Convert(Convert("0123456789","",link.column),"",link.column)
The inner convert keeps the non-numeric characters and the outer convert removes them.
Mike
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Tue Nov 11, 2008 6:27 pm
Nice! :D
-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 » Wed Nov 12, 2008 2:12 am
... though computationally intensive.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
dsdoubt
Participant
Posts: 106 Joined: Sat Jul 15, 2006 12:17 am
Post
by dsdoubt » Wed Nov 12, 2008 9:16 am
Smartie Mike!!!!!
pnpmarques
Participant
Posts: 35 Joined: Wed Jun 15, 2005 9:27 am
Post
by pnpmarques » Thu Feb 21, 2019 12:57 am
Still an useful solution after so many years