Oracle REGEXP_REPLACE for both space and "%" at the same time-CodePudding

I have following Oracle SQL code:

SELECT TO_NUMBER(TRIM(REGEXP_REPLACE(per_growth, '(%)(\s)')),
                 'FM99999999999999999990D099999999999999999',
                 'NLS_NUMERIC_CHARACTERS = '', ''') AS per_growth
  FROM sometable;

This code supposed to look for percentage sign first then space and exclude them from the result. However, it is showing

ORA-01722: invalid number

error. I am learning sql yet and do not know exact cause. Is it something went wrong with (%)(\s)? The value in the table is 50%

CodePudding user response：

Did you try good, old REPLACE?

select replace(replace(per_growth, '%', ''), ' ', '') as result
from your_table

CodePudding user response：

You can use

REGEXP_REPLACE(per_growth, '( )(%)')

in order to get rid of % sign and whitespace(s) together

TRIM(REPLACE(per_growth,'%'))

to get rid of % sign first, and then leading and trailing spaces next,

before numerical conversion.

CodePudding user response：

You can use TRANSLATE to get rid of all instances of unwanted characters:

SELECT TO_NUMBER(
         TRANSLATE(per_growth, '0% ', '0'),
         'FM99999999999999999990D099999999999999999',
         'NLS_NUMERIC_CHARACTERS = '', '''
       ) as per_growth
FROM   sometable;

Note: TRANSLATE(expr, from_string, to_string) works by swapping all instances of the characters in from_string with the corresponding characters in to_string and if there are more characters in from_string than to_string then the remaining characters are removed. It is faster than using regular expressions and on a par with using REPLACE but it can handle multiple replacements at once, which REPLACE cannot.

If you did want to use the slower REGEXP_REPLACE then you can replace all whitespace characters and all percent characters, whether together or not, using:

SELECT TO_NUMBER(
         REGEXP_REPLACE(per_growth, '[%[:space:]]'),
         'FM99999999999999999990D099999999999999999',
         'NLS_NUMERIC_CHARACTERS = '', '''
       ) as per_growth
FROM   sometable;

Which, for the sample data:

CREATE TABLE sometable (per_growth) AS
SELECT '1%' FROM DUAL UNION ALL
SELECT '%2' FROM DUAL UNION ALL
SELECT '3 %' FROM DUAL UNION ALL
SELECT '4% ' FROM DUAL UNION ALL
SELECT '5,0%' FROM DUAL UNION ALL
SELECT '%%% 123 456 789,0123456 %' FROM DUAL;

Both output:

PER_GROWTH

1

2

3

4

5

123456789.0123456

PER_GROWTH
1
2
3
4
5
123456789.0123456

db<>fiddle here