DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
dsdoubt
Participant



Joined: 15 Jul 2006
Posts: 106

Points: 880

Post Posted: Tue Nov 11, 2008 12:22 pm Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Parallel
OS: Unix
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:
ID | No
1|003A23
2|234-456
3|    2333



Output
--------
Code:
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54519
Location: Sydney, Australia
Points: 295643

Post Posted: Tue Nov 11, 2008 12:56 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
dsdoubt
Participant



Joined: 15 Jul 2006
Posts: 106

Points: 880

Post Posted: Tue Nov 11, 2008 3:05 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54519
Location: Sydney, Australia
Points: 295643

Post Posted: Tue Nov 11, 2008 4:23 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
Mike



Group memberships:
Premium Members

Joined: 03 Mar 2002
Posts: 1020
Location: Tampa, FL
Points: 6593

Post Posted: Tue Nov 11, 2008 4:41 pm Reply with quote    Back to top    

How about a double convert to avoid identifying/listing all possible non-numeric characters:

Code:
Convert(Convert("0123456789","",link.column),"",link.column)


The inner convert keeps the non-numeric characters and the outer convert removes them.

Mike
Rate this response:  
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42969
Location: Denver, CO
Points: 221612

Post Posted: Tue Nov 11, 2008 6:27 pm Reply with quote    Back to top    

Nice! Very Happy

_________________
-craig

Now I've got that feeling once again, I can't explain; you would not understand
this is now who I am. I have become comfortably numb.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54519
Location: Sydney, Australia
Points: 295643

Post Posted: Wed Nov 12, 2008 2:12 am Reply with quote    Back to top    

... 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.
Rate this response:  
Not yet rated
dsdoubt
Participant



Joined: 15 Jul 2006
Posts: 106

Points: 880

Post Posted: Wed Nov 12, 2008 9:16 am Reply with quote    Back to top    

Smartie Mike!!!!!
Rate this response:  
Not yet rated
pnpmarques
Participant



Joined: 15 Jun 2005
Posts: 34

Points: 277

Post Posted: Thu Feb 21, 2019 12:57 am Reply with quote    Back to top    

Still an useful solution after so many years Smile
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours