谁能帮我把下面的语句中的函数,charindex,left转换成实现相同功能的oralce函数,谢谢!
select t1.lh ,
t1.cf wjgcf,
(select cf from tb t2 where charindex('*1',t2.lh) > 0 and left(t2.lh,charindex('*1',t2.lh) - 1) = t1.lh) yccf,
(select cf from tb t3 where charindex('*2',t3.lh) > 0 and left(t3.lh,charindex('*2',t3.lh) - 1) = t1.lh) eccf
from tb t1
where charindex('*',t1.lh) <= 0
select t1.lh ,
t1.cf wjgcf,
(select cf from tb t2 where charindex('*1',t2.lh) > 0 and left(t2.lh,charindex('*1',t2.lh) - 1) = t1.lh) yccf,
(select cf from tb t3 where charindex('*2',t3.lh) > 0 and left(t3.lh,charindex('*2',t3.lh) - 1) = t1.lh) eccf
from tb t1
where charindex('*',t1.lh) <= 0
t1.cf wjgcf,
(select cf from tb t2 where instr('*1',t2.lh) > 0 and substr(t2.lh,1,instr('*1',t2.lh) - 1) = t1.lh) yccf,
(select cf from tb t3 where instr('*2',t3.lh) > 0 and substr(t3.lh,1,instr('*2',t3.lh) - 1) = t1.lh) eccf
from tb t1
where instr('*',t1.lh) <= 0
create table tb(炉号 varchar(10), 成分 varchar(10))
insert into tb values('1' , 'c')
insert into tb values('2' , 'p')
insert into tb values('3' , 'ca')
insert into tb values('2*1', 's')
insert into tb values('2*2', 'si')
goselect t1.炉号 ,
[未加工成分] = t1.成分 ,
[一次成分] = (select 成分 from tb t2 where charindex('*1',t2.炉号) > 0 and left(t2.炉号,charindex('*1',t2.炉号) - 1) = t1.炉号),
[二次成分] = (select 成分 from tb t3 where charindex('*2',t3.炉号) > 0 and left(t3.炉号,charindex('*2',t3.炉号) - 1) = t1.炉号)
from tb t1
where charindex('*',t1.炉号) <= 0drop table tb/*
炉号 未加工成分 一次成分 二次成分
---------- ---------- ---------- ----------
1 c NULL NULL
2 p s si
3 ca NULL NULL(所影响的行数为 3 行)
*/
可能对应substr(str,1,n)