We have both character and numeric values in a field. We need to pick only numeric values from this set of values to insert into an oracle table.
Is there any function using which we can apply a constraint on this field in the transformer stage?
Filtering character values in transformer
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 46
- Joined: Tue Mar 20, 2007 3:30 am
- Location: India
Do you mean that each record will either have number or character values in the field and you want to load the records with numbers in that field?
In which case use the function "num()" as a constraint on the link
returns 1 if a number or an empty string.
If what you mean is that your field contains both letters and numbers and you want to get just the numbers from that field use
In which case use the function "num()" as a constraint on the link
Code: Select all
num(input.field) = 1 and input.field <> ''
If what you mean is that your field contains both letters and numbers and you want to get just the numbers from that field use
Code: Select all
Oconv(input.field, "MCN")
Regards,
Nick.
Nick.
Can you use/access Oconv function in parallel job?nick.bond wrote:Do you mean that each record will either have number or character values in the field and you want to load the records with numbers in that field?
In which case use the function "num()" as a constraint on the link
returns 1 if a number or an empty string.Code: Select all
num(input.field) = 1 and input.field <> ''
If what you mean is that your field contains both letters and numbers and you want to get just the numbers from that field use
Code: Select all
Oconv(input.field, "MCN")
Use CONVERT() function to convert every character thats not numeric to an empty space. Something like
You can add any character that i left out in that huge list.
Code: Select all
CONVERT("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+=-,./;[]\|}{:?><","",in.COL)
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.