CODEWITHSUNDEEP
sqloracle-databasereplacecase
user14696881
user14696881

Reputation:

REPLACE In oracle

I need your help for REPLACE( in Oracle . I need to replace many character in one column in my test it's ok for change 5 to 'test' if I want change again 5 & 4, you have a solution ?

enter image description here

Upvotes: 0

Views: 111

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I don't consider what you want to do as a string operation. Instead, it seems like you want to lookup a value in a reference table. You should have a "resource state" reference table in the database.

If you don't have one in the database you can generate one in the query:

with resource_state_ref as (
      select 4 as resource_state_key, 'TEST' as val union all
      select 5 as resource_state_key, 'TEST' as val 
     )
select ref.val, t.*
from t left join
     resource_state_ref ref
     on t.resource_state_key = ref.resource_state_key

Upvotes: 0

GMB
GMB

Reputation: 222402

If you want to match on the entire value, I would recommend a case expression, which can easily be extended.

Say you want to transcode both 4 and 5 to "TEST":

case when resource_state_key in (4, 5) then 'TEST' end as val

Or if you want a different value:

case resource_state_key
    when 4 then 'TEST'
    when 5 then 'TEST2'
end as val

In Oracle, the latter can also be expressed with vendor-specific function decode():

decode(resource_state_key, 4, 'TEST', 5, 'TEST2') as val

Upvotes: 2

Related Questions