Page 1 of 2
How to separate charcters and integers?
Posted: Thu Mar 15, 2012 1:24 am
by nareshketepalli
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..
Posted: Thu Mar 15, 2012 3:03 am
by ray.wurlod
Use a BASIC Transformer stage (or a server job).
Code: Select all
Char_col <== Oconv(InLink.TheString,"MCA")
Num_col <== Oconv(InLink.TheString,"MCN")
Posted: Thu Mar 15, 2012 11:29 am
by soumya5891
What is the length of the VARCHAR field? And are you using Datastage 8.5?
Posted: Thu Mar 15, 2012 7:04 pm
by qt_ky
How about in a Parallel job using an inverse pair of Convert() functions--one for each output column?
Posted: Thu Mar 15, 2012 11:16 pm
by nareshketepalli
I am using 8.1 and the length is 50.
Posted: Fri Mar 16, 2012 2:14 am
by prajesh
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.
Posted: Fri Mar 16, 2012 6:33 am
by chulett
I doubt that they always occur in the same spot each time, hence the question on how to detect them.
Posted: Fri Mar 16, 2012 6:36 am
by chulett
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!
Posted: Fri Mar 16, 2012 2:10 pm
by soumya5891
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.
Posted: Fri Mar 16, 2012 3:18 pm
by ray.wurlod
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.
Posted: Fri Mar 16, 2012 4:35 pm
by chulett
What "it" won't work?
Posted: Mon Mar 19, 2012 8:44 am
by nirav.parikh
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
Posted: Mon Mar 19, 2012 8:45 am
by qt_ky
Using Convert() still seems like the simplest option...
Have you tried it?
Posted: Mon Mar 19, 2012 9:00 am
by nirav.parikh
That Convert() option should work very well too. Just posted an alternative if someone wants to do that in the sql query itself. :D
Posted: Mon Mar 19, 2012 10:08 am
by jdsmith575210
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