请教如何统计字符串中最后两位字符是一样的个数谢谢

显示全部楼层 · 2014-4-17 06:00:17

请教如何将表一的记录统计为表二的记录谢谢！
即将表一中列名为str的中的字符串按空格拆分统计拆分后的字符串结尾一样的个数
with table1 as(
select 'code1' code ,'2451020245113025414202541220 2541230254124125412312541230' str from dual
union all
select 'code2' code ,'2451020245112025414252541220 2541225254126025412382541230' str from dual
)
select * from table1

表一
code

千问 · 2014-4-17 06:00:17

with table1 as(
select 'code1' code,
'2451020 2451130 2541420 2541220 2541230 2541241 2541231 2541230' str
from dual
union all
select 'code2' code,
'2451020 2451120 2541425 2541220 2541225 2541260 2541238 2541230' str
from dual
),
table2 as(
SELECT DISTINCT code, regexp_substr(str, '[^ ]+', 1, LEVEL) str
FROM table1
CONNECT BY LEVELselect * from table1;
CODESTR
----- ---------------------------------------------------------------
code1 2451020 2451130 2541420 2541220 2541230 2541241 2541231 2541230
code2 2451020 2451120 2541425 2541220 2541225 2541260 2541238 2541230
SQL>
SQL> select code,
2 str,
3 flag "level(结尾尾数)",
4 count(*) "countNum"
5from
6(select a.rn,
7
b.code,
8
b.str,
9
substr(substr(' ' || str || ' ',
10
instr(' ' || str || ' ', ' ', 1, a.rn) + 1,
11
instr(' ' || str || ' ', ' ', 1, a.rn + 1) - instr(' ' || str || ' ', ' ', 1, a.rn) - 1),
12
-2) flag
13 from (select level rn from (select max(length(str) - length(replace(str, ' ', ''))) diff from table1) connect by level = a.rn)
16 group by code, str, flag
17 order by code, str, flag
18/
CODESTR
level(结尾尾数) countNum
----- --------------------------------------------------------------- --------------- ----------
code1 2451020 2451130 2541420 2541220 2541230 2541241 2541231 2541230 20
3
code1 2451020 2451130 2541420 2541220 2541230 2541241 2541231 2541230 30
3
code1 2451020 2451130 2541420 2541220 2541230 2541241 2541231 2541230 31
1
code1 2451020 2451130 2541420 2541220 2541230 2541241 2541231 2541230 41
1
code2 2451020 2451120 2541425 2541220 2541225 2541260 2541238 2541230 20
3
code2 2451020 2451120 2541425 2541220 2541225 2541260 2541238 2541230 25
2
code2 2451020 2451120 2541425 2541220 2541225 2541260 2541238 2541230 30
1
code2 2451020 2451120 2541425 2541220 2541225 2541260 2541238 2541230 38
1
code2 2451020 2451120 2541425 2541220 2541225 2541260 2541238 2541230 60
1
9 rows selected

千问 · 2014-4-17 06:00:17

>select code,str,xx,count(xx) from (select code,str,substr(regexp_substr(str,'[^ ]+',1,level),-2,2) xx from t connect by prior code=code and prior dbms_random.value() is not null and level<=regexp_count(str,'[ ]+')+1) group by code,str,xx order by 1;

千问 · 2014-4-17 06:00:17

写一个函数来处理较好

请教 如何统计字符串中最后两位字符是一样的个数 谢谢

请教如何统计字符串中最后两位字符是一样的个数谢谢