17,088
社区成员
发帖
与我相关
我的任务
分享
select length(REGEXP_REPLACE('I am 5 years old','[ ]*[[:alnum:]]+[ ]*','@')) from dual
--补充:
--此函数可以过滤其他标点符号(如逗号,分号。。),只要标点符号紧跟在单词后面就OK!
CREATE OR REPLACE FUNCTION get_words(word IN VARCHAR2 )
RETURN NUMBER
IS
num NUMBER:=0;
BEGIN
FOR i IN 1..Length(word) LOOP
IF SubStr(word,i,1) = Chr(32) AND SubStr(word,i-1,1)!=Chr(32) THEN
num:=num+1;
END IF ;
END LOOP;
RETURN num+1;
END;
--测试:
SELECT get_words('My name is paddy, I am 23 years old; I like CSDN.') words FROM dual;
--结果:
WORDS
--------
12
select length('I am 5 years old')-length(regexp_replace('I am 5 years old',' ',''))+1 from dual
--忘记了你说不止一个空格,这个是最终版!OK!
CREATE OR REPLACE FUNCTION get_words(word IN VARCHAR2 )
RETURN NUMBER
IS
num NUMBER:=0;
BEGIN
FOR i IN 1..Length(word) LOOP
IF SubStr(word,i,1) = Chr(32) AND SubStr(word,i-1,1)!=Chr(32) THEN
num:=num+1;
END IF ;
END LOOP;
RETURN num+1;
END;
SELECT get_words('I am 5 years old') FROM dual;
CREATE OR REPLACE FUNCTION get_words(word IN VARCHAR2 )
RETURN NUMBER
IS
num NUMBER:=0;
BEGIN
FOR i IN 1..Length(word) LOOP
IF SubStr(word,i,1) = Chr(32) THEN
num:=num+1;
END IF ;
END LOOP;
RETURN num+1;
END;
SELECT get_words('I am 5 years old') FROM dual;
create or replace function f_getcount(a varchar2) return number is
cnt number;
i number;
str varchar2(4000);
flag number; -- 上一个字符是否空格 1 是 0 不是
begin
str := a || ' ';
cnt := 0;
flag := 1;
for i in 1..length(str) loop
-- 如果遇到空格
if substr(str,i,1) = ' ' then
-- 判断前面是否是连续的空格,不是则认为新的单词
if flag = 0 then
cnt := cnt + 1;
end if;
end if;
if substr(str,i,1) = ' ' then
flag := 1;
else
flag := 0;
end if;
end loop;
return cnt;
end ;
--试试这个
create or replace function fun_sumzf2(v_char varchar2) return number
as
num number:=0;
str varchar2(100);
j number;
begin
str:=trim(v_char);
select length(str||' ')-length(replace(str||' ',' ','')) into j from dual;
for i in 1..j loop
if instr(str,' ',1,i)!=instr(str,' ',1,i+1)-1 then
num:=num+1;
end if;
end loop;
return num;
end;
--测试的结果
SQL> select fun_sumzf2('I 2') from dual
2 /
FUN_SUMZF2('I2')
----------------
2
SQL> select fun_sumzf2('I 2 b') from dual
2 /
FUN_SUMZF2('I2B')
-----------------
3
SQL> select fun_sumzf2('I 2 b ') from dual
2 /
FUN_SUMZF2('I2B')
-----------------
3
SQL> select fun_sumzf2('2') from dual
2 /
FUN_SUMZF2('2')
---------------
1