求元音字母['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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29867/viewspace-832525/