ITPub博客

首页 > 应用开发 > IT综合 > [算法]求元音字母在单词中第一次出现的位置.

[算法]求元音字母在单词中第一次出现的位置.

原创 IT综合 作者:lastwinner 时间:2006-05-03 23:29:17 0 删除 编辑

求元音字母['a', 'e', 'i', 'o', 'u']在单词中第一次出现的位置. 没有时,为'0',如:
you(= 2), by (= 0), translate (= 3), i (= 1), Other (= 1)等, 要求用SQL实现!

测试用例:

create table test (word varchar2(30));

insert into test values('A');
insert into test values('quick');
insert into test values('brown');
insert into test values('fox');
insert into test values('jumps');
insert into test values('over');
insert into test values('the');
insert into test values('laze');
insert into test values('dog');
insert into test values('by');
insert into test values('my');

原帖地址:http://www.itpub.net/539893.html

[@more@]

我的方法:
SQL> SELECT WORD, DECODE(POS, LEN+1, 0, POS) MP FROM
  2  
(SELECT WORD, VOWEL, MIN(DECODE(POS, 0, LEN+1, POS)) POS, LEN FROM
  3  
(
  
4  SELECT T.WORD, VW.VOWEL, INSTR(UPPER(T.WORD),VW.VOWEL) POS, LENGTH(T.WORD) LEN FROM
  5  TEST T
,
  
6  (          SELECT UPPER(SUBSTR(STR,ST+1,EN-ST-1)) VOWEL
  7                     FROM
  8                     
(SELECT STR, DECODE(RN,1,0,INSTR(STR,SEP,1,RN-1)) ST ,
  
9                             DECODE(INSTR(STR,SEP,1,RN), 0, LENGTH(STR)+1, INSTR(STR,SEP,1,RN)) EN
 10                             FROM
11                                     
(SELECT ROWNUM RN, STR, SEP FROM
 12                                             
(SELECT 'a/e/i/o/u' STR, '/' SEP FROM DUAL)
 
13                                     CONNECT BY ROWNUM<=LENGTH(STR)-LENGTH(REPLACE(STR,SEP,''))+1
 14                                     
)
 
15                     ) WHERE SUBSTR(STR,ST+1,EN-ST-1) IS NOT NULL
 16  
) VW
 17  
)
 
18  GROUP BY WORD
 19  
)
 
20  /


WORD                                   MP

------------------------------ ----------

A                                       1
brown                                   3
by                                      0
dog                                     2
fox                                     2
jumps                                   2
laze                                    2
me                                      2
my                                      0
over                                    1
quick                                   2
the                                     3


已选择12行。

hmxxyy的方法:
select word, instr(translate(lower(word), 'aeiou', 'XXXXX'), 'X')
pos from test

WORD                                                                POS

------------------------------------------------------------ ----------

A                                                                     1
quick                                                                 2
brown                                                                 3
fox                                                                   2
jumps                                                                 2
over                                                                  1
the                                                                   3
laze                                                                  2
dog                                                                   2
by                                                                    0
my                                                                    0

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29867/viewspace-832525/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-12

  • 博文量
    223
  • 访问量
    2815133