oracle replace '0' with 'N/A'-CodePudding

I would like to replace data in column RANDOM that equal to '0', NULL, 'NONE', 'NA' WITH 'N/A'. I used nested REPLACE as below:

SELECT IDNO, 
REPLACE (
    REPLACE (
        REPLACE(
            REPLACE(RANDOM, 'NONE|NA', 'N/A'),
        '0','N/A'),
    '-',''),
NULL, 'N/A') AS NUMBER
FROM TABLE1

It does replace data '0' with 'N/A'. However, it does replace other data that contain 0 in that too. Example: 04110 change to N/A411N/A

enter image description here

I want to replace the one that exactly have value '0' only.

SELECT IDNO, 
REPLACE (
    REPLACE (
        REPLACE(
            REPLACE(RANDOM, 'NONE|NA', 'N/A'),
        '/0','N/A'),
    '-',''),
NULL, 'N/A') AS NUMBER
FROM TABLE1

When I do this (add \ in front of 0) it does not change any of the 0 to 'N/A'

How do I replace data with the exact value '0' with 'N/A' then?

Thank you in advance. :)

CodePudding user response：

... RANDOM that equal to '0', NULL, 'NONE', 'NA' WITH 'N/A'

"Equal" means that there's nothing else in that column. In that case, use CASE:

select idno,
  case when random in ('0', 'NONE', 'NA') or radnom is NULL then 'N/A'
       else random
  end as result
from table1

CodePudding user response：

Hint: Check out IF function of your sql server. Try this:

SELECT 
    IDNO,
    IF(Random IS NULL or Random = '0' or Random='NONE' or Random = 'NA', 'N/A', Random) 
FROM
    TABLE1