OraFAQ Forum: JDeveloper, Java & XML » Separate Characters in SQL

Home » Developer & Programmer » JDeveloper, Java & XML » Separate Characters in SQL (SQL Analyzer)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Separate Characters in SQL [message #476111]

Tue, 21 September 2010 07:53

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

Report message to a moderator

Re: Separate Characters in SQL [message #476220 is a reply to message #476111]

Wed, 22 September 2010 02:53

_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.

Report message to a moderator

Previous Topic:	Pass data to another page
Next Topic:	ORA-01000: maximum open cursors exceeded

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 17:32:26 CST 2025