PL SQL replace conditionally suggestion

Question

I need to replace the entire word with 0 if the word has any non-digit character. For example, if digital_word='22B4' then replace with 0, else if digital_word='224' then do not replace.

SELECT replace_funtion(digital_word,'has non numeric character pattern',0,digital_word)
FROM dual;

I tried decode, regexp_instr, regexp_replace but could not come up with the right solution. Please advise.

Thank you.

Answer 1

the idea is simple - you need check if the value is numeric or not

script:

with nums as
(
select '123' as num from dual union all
select '456' as num from dual union all
select '7A9' as num from dual union all
select '098' as num from dual 
)
select n.* 
 ,nvl2(LENGTH(TRIM(TRANSLATE(num, ' +-.0123456789', ' '))),'0',num)
from nums n

result

1   123 123
2   456 456
3   7A9 0
4   098 098

see more articles below to see which way is better to you

How can I determine if a string is numeric in SQL?

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15321803936685

How to tell if a value is not numeric in Oracle?

Answer 2

You might try the following:

SELECT CASE WHEN REGEXP_LIKE(digital_word, '\D') THEN '0' ELSE digital_word END
  FROM dual;

The regular expression class \D matches any non-digit character. You could also use [^0-9] to the same effect:

SELECT CASE WHEN REGEXP_LIKE(digital_word, '\D') THEN '0' ELSE digital_word END
  FROM dual;

Alternately you could see if the value of digital_word is made up of nothing but digits:

SELECT CASE WHEN REGEXP_LIKE(digital_word, '^\d+$') THEN digital_word ELSE '0' END
  FROM dual;

Hope this helps.

Answer 3

The fastest way is to replace all digits with null (to simply delete them) and see if anything is left. You don't need regular expressions (slow!) for this, you just need the standard string function TRANSLATE().

Unfortunately, Oracle has to work around their own inconsistent treatment of NULL - sometimes as empty string, sometimes not. In the case of the TRANSLATE() function, you can't simply translate every digit to nothing; you must also translate a non-digit character to itself, so that the third argument is not an empty string (which is treated as a real NULL, as in relational theory). See the Oracle documentation for the TRANSLATE() function. https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions216.htm#SQLRF06145

Then, the result can be obtained with a CASE expression (or various forms of NULL handling functions; I prefer CASE, which is SQL Standard):

with 
     nums ( num ) as (
       select '123' from dual union all
       select '-56' from dual union all
       select '7A9' from dual union all
       select '0.9' from dual 
     )
-- End of simulated inputs (for testing only, not part of the solution).
-- SQL query begins BELOW THIS LINE. Use your own table and column names.
select num,
       case when translate(num, 'z0123456789', 'z') is null 
            then num
            else '0'
       end as result
from   nums
;

NUM  RESULT
---  ------
123  123
-56  0
7A9  0
0.9  0

Note: everything here is in varchar2 data type (or some other kind of string data type). If the results should be converted to number, wrap the entire case expression within TO_NUMBER(). Note also that the strings '-56' and '0.9' are not all-digits (they contain non-digits), so the result is '0' for both. If this is not what you needed, you must correct the problem statement in the original post.

Answer 4

Something like the following update query will help you:

update [table] set [col] = '0'
where REGEXP_LIKE([col], '.*\D.*', 'i')