将sql server的语句转oracle

Question

用DECODE(INSTR(weighupsite,'区'),0,weighupsite,substr(weighupsite,1,INSTR('区',weighupsite))

Answer 1

select 
   substr(weighupsite,1,coalesce ( decode(sign(instr(weighupsite,'区')),1,instr(weighupsite,'区')),
                                   decode(sign(instr(weighupsite,'站')),1,instr(weighupsite,'站'))
                                  )
         ) as site 
from table1

Answer 2

weighupsite的内容如下AAAA区XXXXXXX
BBBB区XXXXXXX
CCCC站XXXXXXX
.
.
我要显示为
AAAA区
BBBB区
CCCC站

Answer 3

select DECODE(INSTR(weighupsite,'区'),0,DECODE(INSTR(weighupsite,'站'),0,weighupsite,substr(weighupsite,1,INSTR('站',weighupsite)))
,substr(weighupsite,1,INSTR('区',weighupsite))) as site from table1

Answer 4

coalesce 是什么，不支持啊

Answer 5

coalesce(expr1,expr2,....exprn) 返回第一个不为空的值。

Answer 6

coalesce 不支持的话可以改用nvl：select 
   substr(weighupsite,1,nvl(decode(sign(instr(weighupsite,'区')),1,instr(weighupsite,'区')),
                            decode(sign(instr(weighupsite,'站')),1,instr(weighupsite,'站'))
                            )
         ) as site 
from table1