EXTRACT Numbers only
Moderators: chulett, rschirm, roy
EXTRACT Numbers only
We have data on a Teradata table and it contains Numbers followed by some thing (seriously something.. not space..not tab ..not sure what).
Trim while selecting the data or Trim / StripWhitespace in datastage wont get rid of it.
It was loaded from mainframe.. so may be someone goofed up. Thats not my problem.
I am reading the data and I want to get rid of it.
On server jobs we could use the inbuilt DIGITS transform for it to get rid of such stuff.
Code for DIGITS transform "OCONV(%ARG1%,"MCN")"
I know we cannot use OCONV in parallel.
So what are my option on a parallel job.
Trim while selecting the data or Trim / StripWhitespace in datastage wont get rid of it.
It was loaded from mainframe.. so may be someone goofed up. Thats not my problem.
I am reading the data and I want to get rid of it.
On server jobs we could use the inbuilt DIGITS transform for it to get rid of such stuff.
Code for DIGITS transform "OCONV(%ARG1%,"MCN")"
I know we cannot use OCONV in parallel.
So what are my option on a parallel job.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can look into writing your own C/C++ code. Look into the isprint() function. It checks if the character is printable or not. Search for pxEreplace() here in dsxchange and see how i am taking in a string and looping through each and every character.
Once you get the looping mastered pass it through the isprint(), if its true, keep it, if not skip it. Return the end result and that will give you string without unprintable characters.
Once you get the looping mastered pass it through the isprint(), if its true, keep it, if not skip it. Return the end result and that will give you string without unprintable characters.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Which isn't quite what the OP was asking for but which would probably have the same end result in this situation. You could also build another very similar function that checks each character for a numeric digit and only passes that out as the result, that would match what the Server DIGITS transform does.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
chulett - Thats exactly what I am looking for a new function which would do what the DIGITS does.
But I dont know how to go about it.
Writing a C program for this would be a too much to ask. There has to be a simpler way to check for numbers.
Ray. I am not sure how to read the data in Hex format in Teradata. But I dont want to know the hex values of what else is there. Instead I would still like to write a generic function that just does what the digits transform does. extract numbers from a string.
Thanks
Sarju Shah
But I dont know how to go about it.
Writing a C program for this would be a too much to ask. There has to be a simpler way to check for numbers.
Ray. I am not sure how to read the data in Hex format in Teradata. But I dont want to know the hex values of what else is there. Instead I would still like to write a generic function that just does what the digits transform does. extract numbers from a string.
Thanks
Sarju Shah
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This is a useful technique: it has been posted previously.
The inner Convert() function returns a string of all non-numeric characters in the field; the outer Convert() function removes all of those leaving only numeric characters.
Code: Select all
Convert(InLink.TheField, Convert(InLink.TheField, "0123456789", ""), "")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.