在SQL中，拆分字符

/**
需求
找出ID相同，name中有两个字符相同的ID
*****/

-----创建测试表 tbl
CREATE TABLE tbl AS
SELECT '1001' id, '张三风' NAME FROM dual
UNION
SELECT '1001' id, '张三丰' NAME FROM dual
UNION
SELECT '1002' id, '李四' NAME FROM dual
UNION
SELECT '1002' id, '李四abcd' NAME FROM dual
UNION
SELECT '1003' id, '风清扬' NAME FROM dual
UNION
SELECT '1003' id, '凤清扬' NAME FROM dual
UNION
SELECT '1004' id, '程学琳' NAME FROM dual
UNION
SELECT '1004' id, '程琳' NAME FROM dual
UNION
SELECT '1005' id, '令狐冲' NAME FROM dual
union
SELECT '1005' id, '令盈盈' NAME FROM dual;

------找出rowid 与 id
with
    t1 as (select rowid rd,id,name from tbl),
    t2 as (select rownum rn from dual connect by rownum<=(select max(length(name)) from t1)),
    t3 as (select t1.rd,t1.id,regexp_substr(t1.name,'(.)',t2.rn) str from t1,t2),
    t4 as (select * from t3 where str is not null)
select a.rd ard,a.id aid,count(a.id) count_a from t4 a,t4 b
    where a.rd<>b.rd and a.id=b.id and a.str=b.str
    group by a.rd,a.id having count(a.id)>=2
    order by a.rd,a.id;

 

总结：这种写法的亮点在于运用了正则表达式regexp_substr(t1.name,'(.)',t2.rn)

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

转载于:http://blog.itpub.net/26977915/viewspace-755477/