How to separate charcters and integers?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 36
- Joined: Mon Jun 28, 2010 11:24 pm
- Location: seepz
How to separate charcters and integers?
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..
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..
NARESHKUMAR
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Use a BASIC Transformer stage (or a server job).
Code: Select all
Char_col <== Oconv(InLink.TheString,"MCA")
Num_col <== Oconv(InLink.TheString,"MCN")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 152
- Joined: Mon Mar 07, 2011 6:16 am
-
- Participant
- Posts: 36
- Joined: Mon Jun 28, 2010 11:24 pm
- Location: seepz
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.
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.
prajesh
-
- Participant
- Posts: 152
- Joined: Mon Mar 07, 2011 6:16 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 14
- Joined: Thu Dec 13, 2007 2:57 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
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
Thanks & Regards
Nirav
Nirav
-
- Participant
- Posts: 14
- Joined: Thu Dec 13, 2007 2:57 am
-
- Participant
- Posts: 14
- Joined: Mon Jan 19, 2009 9:06 pm
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