ORA 01722 : Dealing with invalid number-CodePudding

I am dealing with some data quality issues. I would like to remove all the dots and replace dot with comma in the end and replace negative in the end to front. Below are the examples:

70.693.271.273,20- convert to -70693271273,20 1.312.871.286.870- convert to -1312871286,870

For the first case 70.693.271.273,20- I am using the below syntax which gives me results -70693271273,20

CASE WHEN REGEXP_COUNT("/BIC/ISMVALUE", '\.') >= 1
AND REGEXP_COUNT("/BIC/ISMVALUE", ',') = 1 THEN
to_number(REPLACE(TRIM(BOTH '-' FROM TRIM("/BIC/ISMVALUE")), '.', '') * (-1))
ELSE TO_NUMBER(TRIM("/BIC/ISMVALUE")) END oprl_value

Similarly I would like to get same format results for case 2. Can you please help on how to deal with second case 1.312.871.286.870- ? Thanks

CodePudding user response：

You can specify the group and decimal separators as part of the to_number() call. If the numbers are always negative then you can include that too:

to_number(
    "/BIC/ISMVALUE", 
    '999G999G999G999G999D99MI',
    'NLS_NUMERIC_CHARACTERS=,.'
  )

If you have a mix of positive and negative numbers then the MI element will cause an error; if the positive numbers have a trailing then you could use S instead of MI, but that seems unlikely. So instead you can handle negativity separately via a case expression, and strip the minus sign during conversion:

case when substr("/BIC/ISMVALUE", -1) = '-' then -1 else 1 end
*
to_number(
    rtrim("/BIC/ISMVALUE", '-'),
    '999G999G999G999G999D99',
    'NLS_NUMERIC_CHARACTERS=,.'
  )

db<>fiddle

It's then up to your client or application to decide how to format that number for display - including which character to use as a decimal separator.

CodePudding user response：

Use multiple replace and concatenation

SELECT '-' || REPLACE(REPLACE(TRIM(your_value),'.',','),'-','') AS oprl_value