Skip to Main Content
  • Questions

  • Separating Values From One Column into Multiple Columns

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, mary.

Asked: August 30, 2003 - 9:10 pm UTC

Last updated: June 30, 2006 - 5:11 pm UTC

Version: 9.2.0.1, 8.1.7

Viewed 10K+ times! This question is

You Asked

I am trying to separate the values from one column into multiple columns, i.e., I have a column in my database called IP Address which holds values like 111.111.111.111, but I want to split each set of numbers into it's own separate column.

Sometimes the values of the data are in the format of 111.11.11.11 and other times they are in the format of 111.11.11.111 (the size of the last set of numbers varies).

I'm looking for an example using the best sql function to handle this.

and Tom said...

ops$tkyte@ORA920> select x,
2 substr( x, 1, instr(x,'.')-1 ) x1,
3 substr( x, instr(x,'.')+1, instr(x,'.',1,2)-instr(x,'.')-1 ) x2,
4 substr( x, instr(x,'.',1,2)+1, instr(x,'.',1,3)-instr(x,'.',1,2)-1 ) x3,
5 substr( x, instr(x,'.',1,3)+1 ) x4
6 from t
7 /

X X1 X2 X3 X4
--------------- ---- ---- ---- ----
1.2.3.4 1 2 3 4
11.21.31.41 11 21 31 41
111.21.31.41 111 21 31 41
111.21.31.411 111 21 31 411

ops$tkyte@ORA920>


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

and how we gonna do this on 10g

thierry, August 31, 2003 - 10:07 am UTC

Can this problem not be seen as a regular expression? Will that offer a more elegant implementation for the original question?

Tom Kyte
August 31, 2003 - 1:03 pm UTC

regex is for searches, it doesn't "break up"

that requires a parser...

so, in 10g, it'll look -- well -- exactly the same.

i don't see the inelegance in "find start pos", "calculate length", "substr" myself.

works great

mary, August 31, 2003 - 12:04 pm UTC

thanks Tom

What about this?

Jon, August 31, 2003 - 10:48 pm UTC

I was thinking that rewritting the query like this be less CPU intensive, only doing 3 instr per row instead of 8:

select substr(x, 1, d1-1) x1,
substr(x, d1+1, d2-d1-1) x2,
substr(x, d2+1, d3-d2-1) x3,
substr(x, d3+1) x4
from
(select x,
instr(x,'.',1,1) d1,
instr(x,'.',1,2) d2,
instr(x,'.',1,3) d3
from t);

However, when I tried it out on 100,000 rows, it was actually a few seconds slower. Why?

Thx,
Jon

Tom Kyte
September 01, 2003 - 8:19 am UTC 

you are ascribing procedural constructs to SQL and none can really apply.

your query is really calling it 8 times.

mine calls it 6 -- a quirk of the optimization.  your query is rewritten flat but the duplicate invocations of instr are not factored out.  mine, without any such rewrite, seems to have the duplicate instr calls factored out.

consider:

ops$tkyte@ORA920> create or replace function myinstr( s1 in varchar2, s2 in varchar,
  2       n1 in number default null, n2 in number  default null) return number
  3  is
  4  begin
  5          dbms_application_info.set_client_info(userenv('client_info')+1);
  6          return instr(s1,s2,n1,n2);
  7  end;
  8  /
 
Function created.
 
ops$tkyte@ORA920> drop table t;
Table dropped.
 
ops$tkyte@ORA920> create table t ( x varchar2(15) );
Table created.
 
ops$tkyte@ORA920> insert into t values ( '1.1.1.1' );
1 row created.
 
ops$tkyte@ORA920> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920> select substr(x, 1, d1-1) x1,
  2         substr(x, d1+1, d2-d1-1) x2,
  3         substr(x, d2+1, d3-d2-1) x3,
  4         substr(x, d3+1) x4
  5  from
  6     (select x,
  7          myinstr(x,'.',1,1) d1,
  8          myinstr(x,'.',1,2) d2,
  9          myinstr(x,'.',1,3) d3
 10      from t);
 
X1              X2              X3              X4
--------------- --------------- --------------- ---------------
1               1               1               1
 
<b>ops$tkyte@ORA920> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
8
 </b>
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec dbms_application_info.set_client_info(0);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920> select x,
  2     substr( x, 1, myinstr(x,'.')-1 ) x1,
  3     substr( x, myinstr(x,'.')+1, myinstr(x,'.',1,2)-myinstr(x,'.')-1 ) x2,
  4     substr( x, myinstr(x,'.',1,2)+1, myinstr(x,'.',1,3)-myinstr(x,'.',1,2)-1 ) x3,
  5     substr( x, myinstr(x,'.',1,3)+1 ) x4
  6    from t
  7  /
 
X               X1              X2              X3              X4
--------------- --------------- --------------- --------------- ---------------
1.1.1.1                                         1               1
 <b>
ops$tkyte@ORA920> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
6
 
</b>

Interesting...

Mark Wooldridge, September 01, 2003 - 11:48 pm UTC 

Tom,

Great use of dbms_application_info to understand what is happening, but the implementation of instr has a twist or your implementation of myinstr has an interesting side effect.

Passing null for the position or occurrence of the character provides erroneous results.

select instr('1.2.3.4', '.', null, null) from dual
/
SQL> set null *NULL*
SQL> l
  1* select instr('1.2.3.4', '.', null, null) from dual
SQL> /

INSTR('1.2.3.4','.',NULL,NULL)
------------------------------
*NULL*

1 row selected.
does not return the same result as
select instr('1.2.3.4', '.', 1, 1) from dual
/
SQL> select instr('1.2.3.4', '.', 1, 1) from dual
  2  
SQL> /

INSTR('1.2.3.4','.',1,1)
------------------------
                       2

1 row selected.

after modifying your myinstr function to default the start position and occurrence to 1 and re-executing the test I find that myinstr is executed 8 times.

Would there be a way to use deterministic on the function and identify if it is being re-executed or or just the result being obtained?

Tom Kyte
September 02, 2003 - 7:13 am UTC

cool, didn't even think about that -- passing null's to it is different then not passing anything at all!

so, they are both called 8. deterministic currently only has meaning to function based indexes and materialized views, it is not used anywhere else. so, it would not apply here.

Well how about this then...

Jon, September 02, 2003 - 5:07 am UTC

begin
dbms_application_info.set_client_info(0);
end;

select substr(x, 1, d1-1) x1,
substr(x, d1+1, d2-d1-1) x2,
substr(x, d2+1, d3-d2-1) x3,
substr(x, d3+1) x4
from
(select /*+ no_merge */ x,
myinstr(x,'.',1,1) d1,
myinstr(x,'.',1,2) d2,
myinstr(x,'.',1,3) d3
from t);

select userenv('client_info') calls from dual
CALLS
----------------------------------------------------------------
3
1 row selected


Now we are down to 3...

Tom Kyte
September 02, 2003 - 7:33 am UTC

yup, adding "where rownum >= 0" to the inline view will have a similar effect.

Split the column and create multiple rows

ST, June 30, 2006 - 4:04 pm UTC

How do we split the string in column C to have multiple records. The first characters, 'ABC', before ';' should be concatenated with the characters, 'A200', after the space to the end of ';'. The same for the second set of characters, 'ABCD' which is after the first ';' should be concatenated with the string comming after the first ';' after the space. Here is the sample data and expecting to get the desired output.

Table TEST1
A B C
1 1001 ABC;ABCD A200;A1001
1 1002 ABD;BCD;CDEF;DEFG A201;B102;C1003;D4001

into table TEST2
A B C
1 1001 ABC A200
1 1001 ABCD A1001
1 1002 ABD A201
1 1002 BCD B102
1 1002 CDEF C1003
1 1002 DEFG D4001

Tom Kyte
June 30, 2006 - 5:11 pm UTC

ugh, please look in the *OTHER* place you asked the same exact thing.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.