How to Remove Non Numeric?

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

Post Reply
dsdoubt
Participant
Posts: 106
Joined: Sat Jul 15, 2006 12:17 am

How to Remove Non Numeric?

Post by dsdoubt »

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
--------

Code: Select all

ID | No
1|323
2|234456
3|2333
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 »

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 »

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 »

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
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

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
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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 »

... 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 »

Smartie Mike!!!!!
pnpmarques
Participant
Posts: 35
Joined: Wed Jun 15, 2005 9:27 am

Post by pnpmarques »

Still an useful solution after so many years :)
Post Reply