CODEWITHSUNDEEP
sqloracle-database
user15746603
user15746603

Reputation:

Oracle SQL format mask to_number

I have a column in which I could have negative values in 2 different formats: trailing sign normal minus sign. So far, I've tried the below but one of them fails for each number. Is there a way I could cover all my scenarios?

SELECT
    str,
    to_number(str, 'fm999.99mi') AS opt1,
    to_number(str, 'fm999.99') AS opt2
FROM
    (
        SELECT
            '-92.93' AS str
        FROM
            dual
        UNION ALL
        SELECT
            '92.93-' AS str1
        FROM
            dual
    );

My desired output would be:

str opt1 opt2
-92.93 -92.93 -92.93
-92.93 -92.93 -92.93

EDIT: version Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Upvotes: 1

Views: 4277

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

One approach is to use on conversion error and coalesce(). That is, return NULL instead of an error and choose the first non-NULL value.

For your example:

SELECT str,
       coalesce(to_number(str default null on conversion error, 'fm999.99mi'),
                to_number(str, 'fm999.99')
               ) AS opt

In earlier versions, you would use a CASE expression:

SELECT str,
       (CASE WHEN str LIKE '%-'
             THEN to_number(str, 'fm999.99mi')
             ELSE to_number(str, 'fm999.99')
        END) AS opt

Upvotes: 1

MT0
MT0

Reputation: 167981

You can use string functions to check if the minus sign is at the end of the string and reverse it:

SELECT str,
       TO_NUMBER(
         CASE
         WHEN str LIKE '%-'
         THEN '-' || SUBSTR( str, 1, LENGTH(str) - 1 )
         ELSE str
         END,
         'fm999.99'
       ) AS num
FROM   (
  SELECT '-92.93' AS str FROM DUAL UNION ALL
  SELECT '92.93-' AS str FROM DUAL
);

Which outputs:

STR NUM
-92.93 -92.93
92.93- -92.93

db<>fiddle here

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191275

You could use a case expression to modify the format mask:

to_number(str, 'fm999.99' || case when substr(str, -1) = '-' then 'mi' end)

With your data:

SELECT
    str,
    to_number(str, 'fm999.99' || case when substr(str, -1) = '-' then 'mi' end) AS num
FROM
    (
        SELECT
            '-92.93' AS str
        FROM
            dual
        UNION ALL
        SELECT
            '92.93-' AS str1
        FROM
            dual
    );

gets

STR         NUM
-------- ------
-92.93   -92.93
92.93-   -92.93

db<>fiddle

I don't think the fm is really adding anything here.

Upvotes: 2

Related Questions