Replacing desired numbers in cell

Question

I need to replace cell if student album contains "357" numbers for "000". The format of the column is NUMBER. The number album can looks like this: 90432,90709,83570, so these numbers can be "inside". For number 83570 I should print 80000.

I tried like this:

select student.imie,
    student.nazwisko,
    case
        when student.nralbumu like '%357%' then replace(nralbumu, '%357%', '%000%')
        else student.nralbumu
    end album
from student

but it did not work. I got error like:

00932. 00000 - "inconsistent datatypes: expected %s got %s"

Answer 1

Use REPLACE directly since nothing will happen if the number doesn't contain 357

SELECT TO_NUMBER(REPLACE(TO_CHAR(nralbumu), '357','000'))
FROM STUDENT

or if you don't need the result as a number

SELECT REPLACE(TO_CHAR(nralbumu), '357','000')
FROM STUDENT