How to Exract only number from alphanumeric

ambasta · Post by **ambasta** » Fri Oct 27, 2006 11:35 pm

Hi All,
My requirement is to Populate only number for a column which can contain alphanumeric.The length of Column is 24 and dattype is Varchar.number and alphabets can come at any place.
Ex..if data is coming like hfkhfh12hje4344jbd4j then i need to populate only 1242444. or if input data is hjdghj342475jfhjf8 then i need to populate 3424758.Is there any inbuilt unction in datastage to do this.
Can anyone pleas help me on this and suggest me what i can do for this???Please help me.

Thanks in Advance.

kumar_s · Post by **kumar_s** » Sat Oct 28, 2006 2:08 am

Use Convert('abcdefghijklmnopqrstuvz','',Input.Col), you can add more character like any special character or you can even use Upper functon for case sensitive.

ambasta · Post by **ambasta** » Sat Oct 28, 2006 7:29 am

Thanks a lot Kumar.My problem is resolved now.

juliyas · Post by **juliyas** » Sun Oct 29, 2006 1:27 pm

ambasta wrote:Thanks a lot Kumar.My problem is resolved now.

Oconv(Input.column,"MTS") - retrieve the numeric data from the field

ray.wurlod · Post by **ray.wurlod** » Sun Oct 29, 2006 2:17 pm

Oconv() function is not natively available in parallel jobs. Oconv() with "MTS" is for working with Time data. Had the OP wanted to use Oconv(), the second argument would be "MCN" rather than "MTS", and it would be necessary to use a BASIC Transformer stage (or a server Transformer stage in a server shared container), either of which would have negative implications for throughput.

chulett · Post by **chulett** » Sun Oct 29, 2006 3:00 pm

ray.wurlod wrote:Had the OP wanted to use Oconv(), the second argument would be "MCN"

a.k.a. the DIGITS transform.