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
rki1966
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)

example:

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

Example:

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
_jum
Messages: 577
Registered: February 2008
Senior Member
For example one can use regexp_substr:
--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;
MAVSEC SEC1  SEC2 
------ ----- -----
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: Fri Mar 29 05:03:28 CDT 2024