0

I have the data in a CLOB field shown below.

[1/13/2022-12:43:36-u12178-s320323]Advance Drop[1/14/2022-00:33:15-u0-s0]Drop<BR>

I use Oracle DBMS. I want to separate the first date, that is 1/13/2022, into its own column. I also want to separate the first time, that is 12:43:36, into another column . And last, I want to have the number after the first u, being 12178, in a separate column .

I work in K-12 Education and this is for a work related project. Would appreciate a help. Thank you.

Output:

Col 1 Col 2 Col 3
1/13/2022 12:43:36 12178
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Nelson
  • 1
  • 1
  • Are you "separating into columns" only for creating a report (for human consumption), or are you doing this before saving the data in a table stored in the database? If it's the latter, note that Oracle doesn't support pure "dates" (without a time component), and it does not have a "time" data type at all. It would be better to save the date and time in a single column of `date` data type (which contrary to the name is **always** date-time); if you need to display them in separate column on reports, that's very easy to do if the data is stored as `date` data type. –  Jun 04 '22 at 14:40

1 Answers1

0

You can use REGEXP_SUBSTR() like this

SELECT REGEXP_SUBSTR(col,'[^[-]+',1) AS col1,
       REGEXP_SUBSTR(col,'[^-]+',1,2) AS col2,
       REGEXP_SUBSTR(col,'[^u-]+',1,3) AS col3       
  FROM t -- your table

where the chars u and [ are pruned from respective substrings those are already splitted by hyphens.

Or you can use

SELECT *
  FROM(SELECT REGEXP_SUBSTR(TO_CHAR(col),'[^u[-]+',1,XMLCast(column_value AS INT)) AS val,
              XMLCast(column_value AS INT) AS rn       
         FROM t,
              XMLTable('1 to 3'))
 PIVOT (
         MAX(val) FOR rn IN (1 "Col 1",2 "Col 2",3 "Col 3")
       )

in order to prevent repeating of the function triple times

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55