Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

split a column

714213Jul 27 2010 — edited Jul 29 2010
table: vendor
v_seq number (PK);
v_id number;
v_case varchar2(50);

Current Vendor Table has data like the following 
v_seq	v_id	v_case
1	v001	c100&c1001&c1010
2	v002	c109&c107......
would like to split the data into the following and stored it back to the table( or a different table) ,
I have no way to know how many v_case per v_id 
v_seq	v_id	v_case
1	v001	c100
2	v001    c1001
3	v001	c1010
4	v002	c109
5	v002	c107
I attempt to use regular expression but didn't really got my answer. can u help ?



Tai
This post has been answered by jihuyao on Jul 28 2010
Jump to Answer

Comments

Aketi Jyuuzou
I like case expression :D
with table1 as  (
select 'xx' col1 from dual union all 
select 'yy' col1  from dual union all
select '01' col1  from dual union all
select '02' col1  from dual union all
select '03' col1  from dual union all
select '04' col1  from dual   ),
table2 as (
select 1 col1 from dual union all
select 2 col1 from dual union all
select 3 col1 from dual union all
select 4 col1 from dual )
select *
  from table1 a,table2 b
 where case when not RegExp_Like(a.col1,'^[0-9]+$')
            then 0
            when to_number(a.col1) = b.col1 then 1
            else 0 end = 1;

CO  COL1
--  ----
01     1
02     2
03     3
04     4
Frank Kulash
Answer
user642297 wrote:
Hello experts,

I have two tables ...
create table1
(
col1 varchar2(20)
);

create table2
(
col1 number(10)
);
with their data as below
Above, you said that table2.col1 was a NUMBER.
Below, it's a VARCHAR2: 
with table1 as
(  select 'xx' col1 from dual union all 
select 'yy' col1  from dual union all
select '01' col1  from dual union all
select '02' col1  from dual union all
select '03' col1  from dual union all
select '04' col1  from dual 
),table2 as 
(
select '01' col1 from dual union all
select '02' col1 from dual union all
select '03' col1 from dual union all
select '04' col1 from dual 
)
Woudn't a better approximation of your real tables be: 
   ),table2 as 
    (
      select 1 col1 from dual union all
      select 2 col1 from dual union all
      select 3 col1 from dual union all
      select 4 col1 from dual 
     )
?
My question
how would i join table1.col1 to table2.col1?? since they are different data types ....i tried to use To_char but then i got 0 zero's,
is ther is any functions i can use ??
Post your code. I think the best way is to use TO_CHAR, but maybe you were using it differently. 
SELECT	*
FROM	table1
JOIN	table2	ON	NVL ( LTRIM (table1.col1, '0')
			    , '0'
			    )	= TO_CHAR (table2.col1)
;
This assumes that table1.col1 never contains decimal points, unary + signs, exponents, or other things like that.
It will handle negative numbers, but if table1.col1 contains leading 0's after the minus sign (e.g. '-0012'), then the solution is a little more complicated.
Marked as Answer by 645300 · Sep 27 2020
645300
Thanks you so much frank!! That worked to me !!
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 26 2010
Added on Jul 27 2010
14 comments
1,992 views