Home » SQL & PL/SQL » SQL & PL/SQL » Split varchar field into 2 fields ,SQL (Oracle 10g,XP)
Split varchar field into 2 fields ,SQL [message #448244] Mon, 22 March 2010 05:12 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

I need to separate 1 field into 2 fields. The source is varchar2 and is like:

[VOUCHER]
CGJ0000617
CG0001442
CGJ0001444
CMOV0000200
CXAR00000001
CXAR00000002

Result should seperate numeric value from characters so that the result would be:

[VOUCHER_char] [VOUCHER_num]
CGJ 0000617
CG 0001442
CGJ 0001444
CMOV 0000200
CXAR 00000001
CXAR 00000002


I need one or two SQL statement.Thanks in advance
Re: Split varchar field into 2 fields ,SQL [message #448248 is a reply to message #448244] Mon, 22 March 2010 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ What can be the differents possibilities of data. For instance, does the number start with '0' ALWAYS?
2/ If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.
3/ Use SUBSTR, INSTR or even REGEXP function and post what you tried (in a formatted way, see read OraFAQ Forum Guide, "How to format your post?" section.)

Regards
Michel
Re: Split varchar field into 2 fields ,SQL [message #448254 is a reply to message #448244] Mon, 22 March 2010 05:31 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

No there is no possibility of starting number with '0' ALWAYS .
Re: Split varchar field into 2 fields ,SQL [message #448255 is a reply to message #448254] Mon, 22 March 2010 05:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Regular expressions are what you need:
SQL> with src as (select 'CGJ0000617' col_1 from dual union all
  2               select 'CG0001442'    from dual union all
  3               select 'CGJ0001444'   from dual union all
  4               select 'CMOV0000200'  from dual union all
  5               select 'CXAR00000001' from dual union all
  6               select 'CXAR00000002' from dual)
  7  select regexp_substr(col_1,'^[A-Za-z]+')    part_1
  8        ,regexp_substr(col_1,'[0-9]+$')  part_2
  9  from   src;

PART_1       PART_2
------------ ------------
CGJ          0000617
CG           0001442
CGJ          0001444
CMOV         0000200
CXAR         00000001
CXAR         00000002

6 rows selected.
Re: Split varchar field into 2 fields ,SQL [message #448257 is a reply to message #448255] Mon, 22 March 2010 06:23 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Thank you JRowbottom ; its working fine
Re: Split varchar field into 2 fields ,SQL [message #448260 is a reply to message #448257] Mon, 22 March 2010 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, please accordingly to forum.
I say that but I don't think you will do it.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
Re: Split varchar field into 2 fields ,SQL [message #448280 is a reply to message #448254] Mon, 22 March 2010 08:24 Go to previous messageGo to next message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
rakeshramm wrote on Mon, 22 March 2010 05:31
No there is no possibility of starting number with '0' ALWAYS .


I do not understand this sentence. To me, this means "there is a possibility of starting number as '0' always." If that is the case, this sentence does not make any sense and I do not know how to interpret it.
Re: Split varchar field into 2 fields ,SQL [message #448459 is a reply to message #448244] Tue, 23 March 2010 09:41 Go to previous messageGo to next message
pradeepgn
Messages: 1
Registered: March 2010
Location: BANGALORE
Junior Member
this might help you

Select regexp_substr(col_1,'^[A-Za-z]+')as p,regexp_substr(col_1,'[1234567890]+') as q from (select 'CGJ0000617' col_1 from dual)
Re: Split varchar field into 2 fields ,SQL [message #448460 is a reply to message #448459] Tue, 23 March 2010 09:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I prefer JRowbottom's more concise solution, yours does not bring anything more except that 1) it is not formatted 2) you know that 0-9 is 1234567890, 3) you don't know what is a-z.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
Re: Split varchar field into 2 fields ,SQL [message #448461 is a reply to message #448459] Tue, 23 March 2010 09:53 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
pradeepgn wrote on Tue, 23 March 2010 09:41
this might help you

Select regexp_substr(col_1,'^[A-Za-z]+')as p,regexp_substr(col_1,'[1234567890]+') as q from (select 'CGJ0000617' col_1 from dual)


Which appears to be the same as JRowbottoms solution.
Previous Topic: Trigger
Next Topic: ORA-01461: can bind a LONG value only for insert into a LONG column without using LONG values
Goto Forum:
  


Current Time: Wed Sep 28 17:44:27 CDT 2016

Total time taken to generate the page: 0.11404 seconds