CODEWITHSUNDEEP
sqlregexoracle-databaseoracle10g
user7329642
user7329642

Reputation: 67

Oracle SQL: Take a String and turn into a Logical Table?

I'm looking for some advice when working with a column value which is a string (varchar(4000)) containing comments. I have to perform an extract based on Dates however I have no timestamps other than a timestamp in the string. I need to extract all comments that have been created within the past 6 months. I've been playing with regexp_SUBSTR, regexp_INSTR but due to limitations on 10g I'm not able to use regexp_count which is making life difficult.

The String would look like this;

LOCKED ENTITY: ACCOUNT
LOCKED BY USER: ops
LOCKED AT: 31/05/2004 11:47
CUST NOTES: <Please enter explanation here>
Customer notes are entered here.

UNLOCKED ENTITY: ACCOUNT
UNLOCKED BY USER: ops
UNLOCKED AT: 31/05/2004 11:49
UNLOCK NOTES: <Please enter explanation here>
Test

LOCKED ENTITY: USER
LOCKED BY USER: ops
LOCKED AT: 31/05/2004 11:50
LOCK NOTES: <Please enter explanation here>
Test

UNLOCKED ENTITY: USER
UNLOCKED BY USER: ops
UNLOCKED AT: 24/08/2009 16:47
UNLOCKED NOTES: <Please enter explanation here>

Thankfully the Pieces of data are labelled however I need to extract all 'blocks' above individually so essentially have an ID and 4 records. The ID is another column of this table.

The problem I have is that although the example above shows 4 blocks I could have some with many more or less, there is no definitive number to work with. I've attempted using CONNECT BY and REGECP_COUNT to run through all occurrences but I get no results and regexp_count is 11g onwards.

Ideally I'd like to end up with something looking like this;

ID       ENTITY                          ACTION                     DATE                          LOCK_NOTES
-------  ------------------------------- -------------------------- ----------------------------- ---------------------------------------------------------------------
11652907 LOCKED ENTITY: ACCOUNT          LOCKED BY USER: OPS        LOCKED AT: 11/06/2004 09:11   LOCK NOTES: ACCT Locked
11652907 LOCKED ENTITY: USER             LOCKED BY USER: OPS        LOCKED AT: 11/06/2004 09:58   LOCK NOTES: User Locked
11652907 UNLOCKED ENTITY: USER           UNLOCKED BY USER: OPS      UNLOCKED AT: 11/06/2004 20:05 UNLOCK NOTES: User now aware.

Which I'd then be able to query based on Date and extract the relevant records.

Any help would be greatly appreciated.

Thanks.

Upvotes: 0

Views: 69

Answers (1)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65288

You can use regexp_substr(str,'^.*+$',1,level,'m') to extract each row along with level pseoducolumn within select..connect by level structure to determine row numbers, and group by each six rows :

with tab as
(
select regexp_substr(str,'^.*+$',1,level,'m') as str, level as rn
  from tab0
connect by level <= instr(str,chr(10))+1
)
select max(case when mod(rn,6)=1 then str end) as "ENTITY",
       max(case when mod(rn,6)=2 then str end) as "ACTION",
       max(case when mod(rn,6)=3 then str end) as "DATE",
       max(case when mod(rn,6)=4 then str end) as "LOCK_NOTES"
  from tab
 group by ceil(rn/6);

where Chr(10) is the line feed character

Demo

Upvotes: 1

Related Questions