Oracle SQL convert string to number with exceptions to treat text as 0

Question

I would like to convert a string column to number, with ability to handle exceptions, such as text, in Oracle SQL.

The target is to convert text to 0, and number in string format into number.

Example Input:

ID   Column1
1    01A
2    02A
3    1.30
4    1,30
5    100000
6           (Note: 1 empty space)
7           (Note: Null)

Expected output

ID   Column1
1    0
2    0
3    1.3
4    1.3
5    100000
6    0
7    0

I tried the following SQL command:

select ID, to_number(Column1) As Column1 
from Table1

The error code is ORA-01722 if there is any non-numeric output.

The expected result is to get rid of error ORA-01722, even when input has null, space, text (i.e. anything non-numeric)

You will not be able to do it in single query. After exception the query will stop. As I see in your example delimiter could be a comma or a dot as well , so you should do it in PL/SQL block and give according format for each value of query during convertion. — Seyran, Sep 11 '19 at 07:40
I think we can use SQL query to replace all "," to be ".", all null to be ' ', so it should not be the key issue? select ID, replace(coalesce(Column1, ' '), ',', '.') As Column 1 from Table1 — chinghp, Sep 11 '19 at 08:07
If author use single query he won't be able to catch exception as he wants. I don't know the reason of exception handlig , maybe he needs to write some logs or smth. else. — Seyran, Sep 12 '19 at 05:41

Peter Lang · Answer 1 · 2019-09-11T09:09:53.007

This requires two measures:

Check if there are any non-numeric characters (see this solution)
You could use regex functions instead of TRANSLATE but I expect them to be slower.
Convert to number, accepting both ',' and '.' as decimal markers (see this solution)

Query:

WITH test_data AS
(
  SELECT '01A' AS column1 FROM dual UNION ALL
  SELECT '02A' AS column1 FROM dual UNION ALL
  SELECT '1.30' AS column1 FROM dual UNION ALL
  SELECT '1,30' AS column1 FROM dual UNION ALL
  SELECT '100000' AS column1 FROM dual UNION ALL
  SELECT ' ' AS column1 FROM dual UNION ALL
  SELECT NULL AS column1 FROM dual
)
SELECT
    '''' || column1 || '''' AS column1, -- only to show result, distinct between '' and ' '
    -- Check if there are only numeric characters
    CASE WHEN TRIM( TRANSLATE( column1, '0123456789-,.', ' ') ) IS NULL
      THEN
          NVL(  -- replace NULL by 0
              TO_NUMBER(
                  -- both ',' and '.' should work as decimal marker so replace
                  REPLACE( TRIM( column1 ), ',', '.' ),
                  -- second parameter necessary to allow third
                  '99999999999999999999D99999999999999999999',
                  -- do not rely on NLS settings, use '.' as decimal marker
                  'NLS_NUMERIC_CHARACTERS=''. '''
              ),
          0 )
      ELSE 0  -- default value if not numeric
    END AS result
FROM test_data;

Results:

COLUMN1      RESULT
-------- ----------
'01A'             0
'02A'             0
'1.30'          1.3
'1,30'          1.3
'100000'     100000
' '               0
''                0

honeybees · Answer 2 · 2021-09-23T10:41:57.723

You can do it without any regex, using the validate_conversion() function.

Create the table with data

create table table1
(
    id      number generated as identity
    column1 varchar2(50),
)
/

INSERT INTO TABLE1 (COLUMN1) VALUES ('01A');
INSERT INTO TABLE1 (COLUMN1) VALUES ('02A');
INSERT INTO TABLE1 (COLUMN1) VALUES ('1.30');
INSERT INTO TABLE1 (COLUMN1) VALUES ('1,30');
INSERT INTO TABLE1 (COLUMN1) VALUES ('100000');
INSERT INTO TABLE1 (COLUMN1) VALUES (' ');
INSERT INTO TABLE1 (COLUMN1) VALUES (null);

Do the conversion

select
    column1 as original_value,
    validate_conversion(replace(column1, ',', '.') as number, '999990D00', 'NLS_NUMERIC_CHARACTERS = ''.,''') as is_the_value_convertable,
    case 
        when validate_conversion(replace(column1, ',', '.') as number, '999990D00', 'NLS_NUMERIC_CHARACTERS = ''.,''') = 1
        then nvl(cast(replace(column1, ',', '.') as number, '999990D00', 'NLS_NUMERIC_CHARACTERS = ''.,'''), 0)
        else 0 
        end as converted_value
from
    table1;

Use the appropriate NLS parameter(s) and number format that is appropriate for your use case.

Note: the first two columns are just there for clarity, you can omit them. The second column shows how the validation function works. It outputs 1 when it's possible to convert the value without error, and zero otherwise (instead of an error). The third column is to do the actual conversion, using the validation function to determine whether it should try the conversion, or just output 0. It's wrapped in an nvl() because otherwise inputting a null would return a null, instead of the 0 that you want in the case where the conversion is not possible.

Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/VALIDATE_CONVERSION.html#GUID-DC485EEB-CB6D-42EF-97AA-4487884CB2CD

score 0 · Answer 3 · answered Sep 11 '19 at 08:27

In order to cast a column to number, we will need to eliminate/replace the text we can use a case statement with regex to achieve this.

Please try running this

    Select ID ,
      CASE 
         WHEN NOT regexp_like (   NVL ( trim (replace(column1 , ',' ,'.')) , 0 )  ,         
                '^[0-9]+[\.0-9]*$'     )   
         THEN 0
        ELSE  TO_NUMBER ( NVL ( trim (replace(column1 , ',' ,'.')) , 0 ) )
      END column1
    from table1

Logic by regexp look for anything that's not like a number change it to 0 if its a number cast as number , trim and nvl is to handle space and null.

Note : replace ',' to '.' is to handle 1,3 = 1.2 , but this is bound to fail if there are multiple comma's in the column (we can write a more complex logic to check the comma count , but seems unnecessary ) , ideally 1,3 should be denoted as text and made 0

Hope this helps

score 0 · Answer 4 · answered Sep 11 '19 at 08:35

You can achieve this using the following query:

alter session set NLS_NUMERIC_CHARACTERS = '.,';
SELECT
    YOUR_COLUMN,
    CASE
        WHEN REGEXP_COUNT(YOUR_COLUMN, '[A-Za-z]') > 0 OR TRIM(YOUR_COLUMN) IS NULL THEN 0
        ELSE TO_NUMBER(REPLACE(YOUR_COLUMN, ',', '.'))
    END AS MY_NUMBER
FROM
    YOUR_TABLE

db<>fiddle demo

Cheers!!

score 0 · Answer 5 · answered Sep 11 '19 at 11:38

You can use regexp_like() and case logic:

SELECT (CASE WHEN REGEXP_LIKE(column1, '[0-9]*[.,]+[0-9]+')
             THEN REPLACE(column1, ',', '.')
             ELSE '0'
        END)
FROM test_data;

This is rather careful about handling edge cases:

Only one ./, is allowed.
If there is a decimal point, then there needs to be a digit afterwards (this can be adjusted).

Here is a db<>fiddle. It adds an additional test case of just '.'.

score 0 · Answer 6 · answered Jul 02 '23 at 11:33

0

select ID, to_number(Column1 DEFAULT 0 ON CONVERSION ERROR) As Column1 from Table1

According to this Oracle doc

answered Jul 02 '23 at 11:33

Евлампий Уматов

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 05 '23 at 12:35

Oracle SQL convert string to number with exceptions to treat text as 0

6 Answers6

Linked