Convert String into Numeric, pulling from the left of ":" and then pulling to the right of ":"

Question

Text_value is column with string values. I need to look for ONLY ':' and separate the left from the right and convert to numeric so I can perform an easy calculation. If ':' does not exist I want nulls to return. I am creating a view in Oracle SQL Developer

Text_Value (Column)    
124  
7  
55:20  
73:00  
106:24

This is my code:

to_number(REGEXP_SUBSTR(b.text_value,'[^:]*',1,1)) AS Num,
to_number(REGEXP_SUBSTR(B.text_value,'[^:]*$'))  AS FRACTION2

These are my results:

results in Oracle

I would expect text_values that didn't contain ":" to return as null. That is what I would like to see.

Please post text not images, and include the results you want to see for those values. — Alex Poole, Jul 03 '18 at 21:50

MT0 · Answer 1 · 2018-07-03T22:45:41.750

You can check for a two numbers separated by a colon using the regular expression ^(\d+):(\d+)$ like this:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( text_value ) AS
SELECT '124' FROM DUAL UNION ALL
SELECT '7' FROM DUAL UNION ALL
SELECT '55:20' FROM DUAL UNION ALL
SELECT '73:00' FROM DUAL UNION ALL
SELECT '106:24' FROM DUAL;

Query 1:

SELECT text_value,
       TO_NUMBER( REGEXP_SUBSTR( text_value, '^(\d+):(\d+)$', 1, 1, NULL, 1 ) ) AS num,
       TO_NUMBER( REGEXP_SUBSTR( text_value, '^(\d+):(\d+)$', 1, 1, NULL, 2 ) ) AS fraction2
FROM   table_name

Results:

| TEXT_VALUE |    NUM | FRACTION2 |
|------------|--------|-----------|
|        124 | (null) |    (null) |
|          7 | (null) |    (null) |
|      55:20 |     55 |        20 |
|      73:00 |     73 |         0 |
|     106:24 |    106 |        24 |

Alex Poole · Answer 2 · 2018-07-03T23:04:32.110

You could avoid regular expressions and use instr and substr instead:

select text_value,
  to_number(case when instr(text_value, ':') > 0
    then substr(text_value, 1, instr(text_value, ':') - 1)
    else null end) as num,
  to_number(case when instr(text_value, ':') > 0
    then substr(text_value, instr(text_value, ':') + 1)
    else null end) as fraction2
from b;

That's doing more individual function calls, but they may still perform better than regular expressions, and you could move the instr into an inline view if you wanted to, so you aren't doing that four times (though the optimiser might cache the result anyway).

SQL Fiddle demo using a few of the values from your image plus a couple of other variations you didn't refer to, and which you might want to handle differently.

If those are not handled as you want, you can adjust the case expression conditions, e.g. for num changing to > 1, and for fraction2 comparing with the string length - but it depends what you want to see (and if those scenarios can even exist).

I posted and then deleted an answer very similar to yours - I just realized that the only difference is the `case` expression check for the first returned value. That check is not needed; if `:` is not found in the string, then `instr` will return 0, and `substr` will return `null` as needed. — , Jul 03 '18 at 23:05
I prefer the explicitness and symmetry, but yes; and the redundant call could have a performance impact. It may be useful if my last two made-up values need different handling though. — Alex Poole, Jul 03 '18 at 23:09

Bob Jarvis - Слава Україні · Answer 3 · 2018-07-03T23:09:16.790

You're nearly there. You just need to test to see if the string has a : in it before extracting the 'seconds' portion of the string:

SELECT TEXT_VALUE,
       TO_NUMBER(REGEXP_SUBSTR(text_value,'[^:]*',1,1)) AS MINUTES,
       CASE
         WHEN INSTR(TEXT_VALUE, ':') > 0 THEN TO_NUMBER(REGEXP_SUBSTR(text_value,'[^:]*$'))
         ELSE NULL
       END AS SECONDS
  FROM T;

TEXT_VALUE  MINUTES SECONDS
11          11      (null)
00:38       0       38
(null)      (null)  (null)
69:18       69      18
74:11       74      11
83:43       83      43
00:51       0       51
00:45       0       45
01:42       1       42
7           7       (null)
78:30       78      30
50          50      (null)
03:08       3       8
70:42       70      42
72:24       72      24
123         123     (null)
55:20       55      20

SQLFiddle here

EDIT

You can get rid of the CASE if you change the second regular expression to include, rather than exclude, the colon, then use SUBSTR to grab all characters following the ::

SELECT TEXT_VALUE,
       TO_NUMBER(REGEXP_SUBSTR(text_value,'[^:]*',1,1)) AS MINUTES,
       SUBSTR(REGEXP_SUBSTR(text_value, ':.*$'), 2) AS SECONDS3
  FROM T;

Revised SQLFiddle here

Convert String into Numeric, pulling from the left of ":" and then pulling to the right of ":"

3 Answers3

EDIT