[DB2] Problem with Replace Function

Question

Hello! :-)

In one of my resultsets i have column with char - data. I want to create another column, which carries altered data from the first column. There i want to exchange all 'ö' with oe.

I tried it like this:

Select NAME1,
    case when POSSTR(NAME1, 'ö') is not null then REPLACE(NAME1, 'ö', 'oe')
    end As __NAME1
from xyz;

and it failed. I don't get an error, but the ö is not replaced.

Am i making a mistake or is it simply not possible to perform this operation?

Thanks! Patrick

Answer 1

According to the DB2 documentation, POSSTR returns 0 (not NULL) if the string is not found.

Answer 2

You you want your second column to list all the rows, both changed and unchanged you can simply use:

Select 
    NAME1,
    REPLACE(NAME1, 'ö', 'oe') As __NAME1
from xyz;

also you can test this:

Select 
    NAME1,
    REPLACE(NAME1, 'ö', 'oe') As __NAME1
from 
    xyz
where
    POSSTR(NAME1, 'ö') <> 0