select alisten,length(alisten)-replace(alisten,',','') from tb_admin
CREATE OR REPLACE FUNCTION SCOTT.get_comma_count (instrs VARCHAR2) RETURN NUMBER AS v_length INT; v_count INT; BEGIN v_count := 0; v_length := LENGTH (instrs); IF v_length < 1 THEN RETURN 0; ELSE FOR i IN 1 .. v_length LOOP IF SUBSTR (instrs, i, 1) = ',' THEN v_count:= v_count+1; end if; end loop; return v_count; end if;
end ; /select get_comma_count(alisten) from tb_admin;
--一楼的方法很巧妙,不过是不是应该加个LENGTH? select alisten,length(alisten)-length(replace(alisten,',','')) from tb_admin
select sum(decode(instr(alisten,'1,2,3,4,5,6,7,8,'),1,1,0)) from tb_admi;
RETURN NUMBER
AS
v_length INT;
v_count INT;
BEGIN
v_count := 0;
v_length := LENGTH (instrs); IF v_length < 1
THEN
RETURN 0;
ELSE
FOR i IN 1 .. v_length
LOOP
IF SUBSTR (instrs, i, 1) = ','
THEN
v_count:= v_count+1;
end if;
end loop;
return v_count;
end if;
end ;
/select get_comma_count(alisten) from tb_admin;
--一楼的方法很巧妙,不过是不是应该加个LENGTH?
select alisten,length(alisten)-length(replace(alisten,',','')) from tb_admin
12楼的方法有点意思,正则表达式很强大呀,我也写一个另类的方法.Select Count(*)
From (Select '1,2,3,4,5,6,7,8,' v From dual)
Connect By instr(v, rownum) > 0;
From (Select '1,2,3,4,5,6,7,8,' v From dual)
Connect By instr(v, ',', 1, rownum) > 0;漏了点东西.抱歉