Filtering character values in transformer

[bi_fujitsu]

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?

[nick.bond]

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

Code: Select all

num(input.field) = 1 and input.field <> ''

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

Code: Select all

Oconv(input.field, "MCN")

[rafik2k]

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

Code: Select all

num(input.field) = 1 and input.field <> ''

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

Code: Select all

Oconv(input.field, "MCN")

Can you use/access Oconv function in parallel job?

[nick.bond]

Ah - no sorry, I was thinking in Server mode! Doing night shift and a bit tired!

[DSguru2B]

Use CONVERT() function to convert every character thats not numeric to an empty space. Something like

Code: Select all

CONVERT("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+=-,./;[]\|}{:?><","",in.COL)

You can add any character that i left out in that huge list.