Oracle converts a string to a number

Recently, a field value of a character type in the Oracle database was used to convert it to a number and save it to another new field. Oracle has a ToNumber function, however, it should be noted that this function can only convert the string format to a correct number, that is, once the string is empty, contains special characters, spaces, and so on, an error will be thrown, one of the following methods is provided to successfully solve the above problems:

TO_NUMBER (decode (Lower(Nvl (Replace(Translate (strTargetField,'$ %',''),'',''), 0 )),Upper(Nvl (Replace(Translate (strTargetField,'$ %',''),'',''), 0), 1, 0), 1, nvl (Replace(Translate (strTargetField,'$ %',''),'',''), 0), 0) numResult

It should be noted that the nvl function also uses the translate function to replace the specified special characters, because the fields in my example may contain the dollar sign (%, I also think this type of characters is the correct value. Therefore, you need to filter out these characters before performing numerical conversion on the remaining parts.