2

I have records formatted as such:

CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

What I am trying to accomplish with a REGEX case statement in Oracle SQL is to have a field in a view that lists the opening alphas in one column, the following digits in a second column, and the two concatenated in a third column. The beginning of the record will have two or three alphas followed possibly by a blank space, a /, a -, or a digit. How can I separate these out? I've tried:

  case
  when subj_no like '%' then
     regexp_substr(subj_no, '^[A-Z]+') subj_no
  end subj_no,

The end result for the given data should be:

COL 1           COL 2    COL 3

CBS405 505      CBS      405 505
BR404 504 604   BR       404 504 604
AP490           AP       490
APR501          APR      501
MP300           MP       300
ACX501          ACX      501
ACX400          ACX      400

How can I use a regular expression in a case statement to arrive at that output?

4

May I suggest doing it in a couple of passes:

SQL> select * from records;

SUBJ_NO
--------------------------------------------------------------------------------
CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

7 rows selected.

SQL> l
  1  with strip as(
  2  select translate(subj_no, '/-', '  ') subj_no
  3    from records
  4  )
  5  , reg as (
  6  select regexp_substr(subj_no,'^[A-Z]{2,3}') col_2
  7   , trim(regexp_substr(subj_no,'[0-9 ]+')) col_3
  8    from strip
  9  )
 10  select col_2||col_3 col_1
 11   , col_2
 12   , col_3
 13*   from reg
SQL> /

COL_1                COL_2      COL_3
-------------------- ---------- --------------------
CBS405 505           CBS        405 505
BR404 504 604        BR         404 504 604
AP490                AP         490
APR501               APR        501
MP300                MP         300
ACX501               ACX        501
ACX400               ACX        400

7 rows selected.

Strip replaces the slashes and dashes with spaces so they don't get in the way later.

Reg rips off your regexp and adds another one that finds the first group of digits and spaces, trimmed for neatness.

Last one takes the results and formats them as you requested.

Edit: If I've understood your comment below then you have all those values in a single row. Slice it up with a pipelined function.

SQL> select * from records;

SUBJ_NO
----------------------------------------------------
CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

1 row selected.

SQL> create or replace function get_records return tt_subj_no pipelined as
  lv_recs  records.subj_no%TYPE;
  lv_subj  varchar2(100);
  lv_start number := 0;
  lv_end   number := 10;
begin
  select *
    into lv_recs
    from records;

  lv_end := instr(lv_recs, chr(10));
  while lv_end > 0
  loop
    pipe row(substr(lv_recs, 1, lv_end));
    lv_recs := substr(lv_recs, lv_end + 1);
    lv_end := instr(lv_recs, chr(10));
  end loop;
  pipe row(lv_recs);
end;
/

Function created.

SQL> select * from table(get_records);

COLUMN_VALUE
----------------------------------------------------
CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

7 rows selected.

Stick that in the original query and you should be good to go.

|improve this answer|||||
  • Very elegant. Much better than the mess I was just about to post! – Philᵀᴹ Apr 24 '15 at 21:29
  • Bold attempt, but the code adapted for my query does not have correct syntax. The color-coded Oracle editor tells me it is not going to run before I get it all keyed in. I need other options. The original data is a single field in a table already stored. – Uncle_Jake Apr 24 '15 at 23:36
  • Syntax highlighters can be wrong. More specifically they don't give ORA errors. Adapt the code until it breaks and then tell us what Oracle thinks of it. Add the extra case to the test data and we'll see what we can do. – Chris Sinclair Apr 24 '15 at 23:46
  • I think I see what you meant in your comment. I've added a bunch more code to slice up your record. Note that CHR(10) is the line break because I'm on Unix. – Chris Sinclair Apr 25 '15 at 10:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.