sql?

Question

interface/rec1066026640?msgid=1369450&servicestype=173&citynumber=20&cityname=广州&savetime=time&reserved=20570827919335649098&cp=0&userid=51
请问我想查出表记录中不同的reserved=值，要怎样写？

Answer 1

表结构在哪里?
上面那么长的信息是不是全部放在一个字段里?如果存储那么多信息,首先判断reserved的起止位置?以及它的值终止位置;
由上面的值用replace()去掉"reserved=",剩下的值就是reserved的值,这里只提供一个思路给你了...

Answer 2

function f(s varchar2) return varchar2 deterministic
is
ret varchar2(32);
str varchar2(16):='&reserved=';
i number;
j number;
begin
i:=instr(s, str);
if i<1 then
  return ret;
end if;
i := i + length(str);
j := instr(s, '&', i);
ret := substr(s, i, j-i);
return ret;
end f;
select distinct f(column) from table;
要提高效率可以加个函数索引

Answer 3

select substr(
'interface/rec1066026640?msgid=1369450&servicestype=173& citynumber=20&cityname=广州&savetime=time&reserved=20570827919335649098 &cp=0&userid=51'
,INSTR('interface/rec1066026640?msgid=1369450&servicestype=173& citynumber=20&cityname=广州&savetime=time&reserved=20570827919335649098 &cp=0&userid=51','reserved=')+9,
instr('interface/rec1066026640?msgid=1369450&servicestype=173& citynumber=20&cityname=广州&savetime=time&reserved=20570827919335649098 &cp=0&userid=51','&',-1)
from 
dual;