How to handle string with only space in oracle sql?-CodePudding

I have a case where I am getting the data from DB and converting the string to a number using TO_NUMBER, but this case fails when the string is an empty string with unknown or space char like

columnA
------
  4444
      
333333

The string '4444' and '333333' is converted to number by there is and error "ora-01722 invalid number" for the 2nd string.

Can this be handled with DECODE or CAST in any way, because I need to use TO_NUMBER any how for further processing?

CodePudding user response：

I hope this could be Insight of your issue.

select 
    TO_NUMBER(trim(colA)),  
    TO_NUMBER(REGEXP_REPLACE(colA,'(^[[:space:]]*|[[:space:]]*$)')),
    regexp_instr(colA, '[0-9.]')    
from 
(
    select '   123' colA from dual
    union all
    select '   ' colA from dual
    union all
    select '.456' colA from dual
)

CodePudding user response：

If all the data within that column is composed of integers, integers with leading and/or trailing whitespaces, null values and only whitespaces then only using TRIM() function will suffice such as

SELECT TRIM(columnA)
  FROM t

and that would be more performant than using functions of regular expressions

But

If the data contains decimal numbers, letters, punctiations and special characters along with whitespaces and null values, then use

SELECT TRIM('.' FROM REGEXP_REPLACE(columnA,'[^[:digit:].[:digit:]]'))
  FROM t

where there is at most one dot character assumed to be between the starting and ending digits. All of the leading and trailing dots are trimmed at the end of the operation provided there is any of them. The other characters are already removed by the regular expression.

If you're sure that there's no trailing or leading dots, then using

SELECT REGEXP_REPLACE(columnA,'[^[:digit:].]')
  FROM t

would be enough

Demo

You can wrap up any of the expressions with TO_NUMBER() function depending on your case at the end