Home » Developer & Programmer » JDeveloper, Java & XML » Separate Characters in SQL (SQL Analyzer)
Separate Characters in SQL [message #476111] Tue, 21 September 2010 07:53 Go to next message
Messages: 8
Registered: July 2010
Location: Dallas, TX
Junior Member
I am trying to figure out how to take one field and break it into two fields.

Field Name = MAVSEC

If the data has a "+" then put it into two fields ( sec1 and sec2)


MAVSEC = HC+FN ( sec1 = HC, Sec2 =FN)
MAVSEC = PV+TK ( sec1 = PV, Sec2 =TK)
MAVSEC = IN ( sec1 = IN, Sec2 = )

If there is no "+" then SEC1 = MAVSEC and Sec2 is Blank

I also want to create Sec3 and Sec4, it will have the same rules as Sec1 and Sec 2, but if MAVSEC contains PV or PVT then put the second sec as Sec3 and leave Sec4 blank


MAVSEC = PV+TK ( sec3 = TK, Sec4 = )
MAVSEC = HC+FN ( sec3 = HC, Sec4 =FN)
MAVSEC = IN ( sec3 = IN, Sec4 = )

Re: Separate Characters in SQL [message #476220 is a reply to message #476111] Wed, 22 September 2010 02:53 Go to previous message
Messages: 498
Registered: February 2008
Senior Member
For example one can use regexp_substr:
WITH data AS
  (SELECT 'HC+FN' mavsec FROM dual UNION ALL
   SELECT 'IN'           FROM dual)
SELECT mavsec,
       regexp_substr(mavsec,'[^+]+',1,1) sec1,
       regexp_substr(mavsec,'[^+]+',1,2) sec2
  FROM data;
------ ----- -----
HC+FN  HC    FN   
IN     IN         

2 rows selected.
Previous Topic: Pass data to another page
Next Topic: ORA-01000: maximum open cursors exceeded
Goto Forum:

Current Time: Sun Oct 04 16:31:02 CDT 2015

Total time taken to generate the page: 0.10279 seconds