0

I have just started to learn and work with Oracle SQL a few months ago, and I have a question that I could not find similar problems on Stack Overflow.

In SQL Oracle, I am trying to find a way that I can read the data from a column and modify (add/subtract) the data. What I have got so far is using replace like here, but I do not want to use multiple replace function to make it work. I am not sure whether you guys understand my question, so I have listed what I have so far below, and I used multiple replace function.

      COMMOD_CODE (Given)  |      MODEL(Desired_result)
                           |
       X2-10GB-LR          |        X2-10GB-LR (same)
       15454-OSC-CSM       |        15454-OSC
       15454-PP64LC        |        15454-PP_64-LC
       CAT3550             |        WS-C3550-48-SMI
       CAT3560G-48         |        WS-C3560G-48PS-S
       CAT3550             |        WS-C3550-48-SMI   
       DWDM-GBIC-30        |        DWDM-GBIC-30.33   



Select 
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(commod.COMMODITY_CODE, 
  '15454-OSC-CSM', '15454-OSC'),
  '15454-PP64LC','15454-PP_64-LC'),
  'CAT3550','WS-C3550-48-SMI'),
  'CAT3560G-48','WS-C3560G-48PS-S'),
  'CAT3550','WS-C3550-48-SMI'),
  'DWDM-GBIC-30','DWDM-GBIC-30.33')
   MODEL,
   NVL(commod.COMMODITY_CODE, ' ') as COMMOD_CODE
FROM tablename.table commod

I got the the answer. However, I think I used a lot of ** REPLACE ** to get it right. So, my question is if there is any easier way to do that instead of using replace multiple times, and make your script look awful.

Is someone able to please give me some guidance?

Thanks in advance,

Community
  • 1
  • 1
harrisonthu
  • 454
  • 3
  • 7
  • 18

2 Answers2

0

Use DECODE or CASE for this, I think. Or, better yet, maybe a mapping table.

Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
0

You can use the DECODE function in this case:

with
  test_data as (
    select '15454-OSC-CSM' as COMMODITY_CODE from dual
    union all select '15454-PP64LC' from dual
    union all select 'CAT3550' from dual
    union all select 'CAT3560G-48' from dual
    union all select 'CAT3550' from dual
    union all select 'DWDM-GBIC-30' from dual
  )
select
  decode(COMMODITY_CODE,
    '15454-OSC-CSM', '15454-OSC',
    '15454-PP64LC', '15454-PP_64-LC',
    'CAT3550', 'WS-C3550-48-SMI',
    'CAT3560G-48', 'WS-C3560G-48PS-S',
    'CAT3550', 'WS-C3550-48-SMI',
    'DWDM-GBIC-30', 'DWDM-GBIC-30.33')
  from test_Data
;

Result:

COL
------------------
15454-OSC         
15454-PP_64-LC    
WS-C3550-48-SMI   
WS-C3560G-48PS-S  
WS-C3550-48-SMI   
DWDM-GBIC-30.33

What the DECODE function does: it checks its first argument - if it is equal to the second argument, then it returns the third argument, otherwise, if it is equal to the 4th argument, it returns the 5th argument, and so on.

Przemyslaw Kruglej
  • 8,003
  • 2
  • 26
  • 41