How to separate charcters and integers?

nareshketepalli · Post by **nareshketepalli** » Thu Mar 15, 2012 1:24 am

Hi,
How to separate the charcters and integers?
Source has the one column ,the datatype is varchar and target has to be two columns.
Ex:

Source -----------------> Target
Column -----------------> Char_col --- Num_col
A1b2c3 -----------------> abc --- 123

Is there any pre-defind fuction?

Thanks in advance..

ray.wurlod · Post by **ray.wurlod** » Thu Mar 15, 2012 3:03 am

Use a BASIC Transformer stage (or a server job).

Code: Select all

Char_col  <==  Oconv(InLink.TheString,"MCA")
Num_col  <==  Oconv(InLink.TheString,"MCN")

soumya5891 · Post by **soumya5891** » Thu Mar 15, 2012 11:29 am

What is the length of the VARCHAR field? And are you using Datastage 8.5?

qt_ky · Post by **qt_ky** » Thu Mar 15, 2012 7:04 pm

How about in a Parallel job using an inverse pair of Convert() functions--one for each output column?

nareshketepalli · Post by **nareshketepalli** » Thu Mar 15, 2012 11:16 pm

I am using 8.1 and the length is 50.

prajesh · Post by **prajesh** » Fri Mar 16, 2012 2:14 am

Hi,

use substring function in transformer to get this output for current requirement.

seq----->txr----->seq2

in transformer u will craete two columns Char_col and Num_col

use substring fun in derivation of corresponding column

inlink.column(1,1):inlink.column(3,1):inlink.column(5,1)-->Char_col
inlink.column(2,1):inlink.column(4,1):inlink.column(6,1)-->num_col

regards,
rajesh.

chulett · Post by **chulett** » Fri Mar 16, 2012 6:33 am

I doubt that they always occur in the same spot each time, hence the question on how to detect them.

chulett · Post by **chulett** » Fri Mar 16, 2012 6:36 am

qt_ky wrote:How about in a Parallel job using an inverse pair of Convert() functions--one for each output column?

Sure seems like that would work!

soumya5891 · Post by **soumya5891** » Fri Mar 16, 2012 2:10 pm

If the column length is greater then 6 ,say 8 then it will not work.According to nareshketepald the length of the field is 50.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 16, 2012 3:18 pm

soumya5891 wrote:If the column length is greater then 6 ,say 8 then it will not work.

Please provide proof of that assertion. I don't believe it is correct.

chulett · Post by **chulett** » Fri Mar 16, 2012 4:35 pm

What "it" won't work?

nirav.parikh · Post by **nirav.parikh** » Mon Mar 19, 2012 8:44 am

For those who have similar requirement and the source oracle you can use some thing like the sql stated below:

select replace(translate('abc123pqr456','0123456789','~'),'~','') from dual;

select replace(translate('abc123pqr456','abcdefghijklmnopqrstuvwxyz','~'),'~','') from dual;

just an example you can tweak is as per your requirement.

Let me know if it works for you.

Regards,
Nirav

qt_ky · Post by **qt_ky** » Mon Mar 19, 2012 8:45 am

Using Convert() still seems like the simplest option...

Have you tried it?

nirav.parikh · Post by **nirav.parikh** » Mon Mar 19, 2012 9:00 am

That Convert() option should work very well too. Just posted an alternative if someone wants to do that in the sql query itself. :D

jdsmith575210 · Post by **jdsmith575210** » Mon Mar 19, 2012 10:08 am

If you're using Oracle and want to use SQL, I would consider regexp_replace.

Code: Select all

 SELECT regexp_replace(my_text, '\D', ''),
  regexp_replace(my_text, '\d', '')
   FROM
  ( SELECT 'Th4is298 is a 12 test' AS my_text FROM dual
  )

REGEXP_REPLACE(MY_TEXT,'\D','') REGEXP_REPLACE(MY_TEXT,'\D','') 
------------------------------- ------------------------------- 
429812                          This is a  test