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: 54501
Location: Sydney, Australia
Points: 295558

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.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
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: 54501
Location: Sydney, Australia
Points: 295558

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.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
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: 42923
Location: Denver, CO
Points: 221366

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

Nice! Very Happy

_________________
-craig

The Old Ones were, the Old Ones are, and the Old Ones shall be. Not in the spaces we know, but between them. They walk serene and primal, undimensioned and to us unseen.
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: 54501
Location: Sydney, Australia
Points: 295558

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

... though computationally intensive.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
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