oracle有关字符分割的查询，2011-09-09 16:55:00在线等。。。

Question

各位高手，帮忙解决下。
比如数据库里的字段A（varchar）组成格式：123,123，123，123（4部分组成）
我要匹配的字符串也是仅由4部分组成，但是至少有三部分是一致的。
像5,123，123，123能匹配，最后三部分是一样的
像123,123，123，123,123不匹配，它由四部分组成
像123,123，123123，123能匹配。

Answer 1

[SYS@orcl] SQL>WITH T1 AS
  2   (SELECT '123,123,123,123' AS COL FROM DUAL
  3    UNION ALL
  4    SELECT '5,123,123,123' AS COL FROM DUAL
  5    UNION ALL
  6    SELECT '123,123,123,123,123' AS COL FROM DUAL
  7    UNION ALL
  8    SELECT '123,123,123123,123' AS COL FROM DUAL)
  9  SELECT DISTINCT COL
 10    FROM (SELECT COL,
 11                 A1,
 12                 COUNT(A1) OVER(PARTITION BY COL, A1) AA1, --这个值是判断有几个值相同的
 13                 COUNT(A1) OVER(PARTITION BY COL) AA2      --这个值是判断有几个组成部分的
 14            FROM (SELECT COL, REGEXP_SUBSTR(COL, '[^, ]+', 1, L) AS A1 --这个子查询是将字符串拆开的
 15                    FROM T1,
 16                         (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100) T2
 17                   WHERE LENGTH(COL) - LENGTH(REPLACE(COL, ',')) + 1 >= L))
 18   WHERE AA1 >= 3 --至少有三个相同
 19     AND AA2 = 4; --只有4部分组成COL
-------------------
123,123,123,123
123,123,123123,123
5,123,123,123

Answer 2

可能是我没有讲清楚，我的意思是我用
一个字符串去匹配数据库中的某个字段比如items（字段items是由多个部分组成，以逗号分隔）
要求：1.库中数据也是由N部分（与待匹配的组成部分个数一样）组成
      2.库中至少匹配其中的N-1部分（要保证相对顺序）
仅举一个例子 :
现在有一个字符串‘A，BB，CCC，DD’（4部分组成）；
数据库中items对应的值为
ID      ITEMS
1       A，BB，CCC，DD，EE（组成部分不匹配）
2       AD，DDD，CCC，DD（仅有2个匹配）
3       AAA，BB，CCC，DD（符合要求）
4       A，BB，CCC  （组成部分不匹配）
5       BB，CCC，DD，AA（顺序不一样）
。
第3条就是我想找到的数据。各位大侠们，小弟不胜感激。

Answer 3

WITH T1 AS
 (SELECT 1 ID, 'A,BB,CCC,DD,EE' ITEMS FROM DUAL
  UNION ALL
  SELECT 2, 'AD,DDD,CCC,DD' ITEMS FROM DUAL
  UNION ALL
  SELECT 3, 'AAA,BB,CCC,DD' ITEMS FROM DUAL
  UNION ALL
  SELECT 4, 'A,BB,CCC' ITEMS FROM DUAL
  UNION ALL
  SELECT 5, 'BB,CCC,DD,AA' ITEMS FROM DUAL)
SELECT *
  FROM T1
 WHERE LENGTH('A,BB,CCC,DD') - LENGTH(REPLACE('A,BB,CCC,DD', ',')) =
       LENGTH(ITEMS) - LENGTH(REPLACE(ITEMS, ','))
   AND (SUBSTR('A,BB,CCC,DD',
               INSTR('A,BB,CCC,DD', ',', 1, 1) + 1,--第一个逗号+1的位置
               LENGTH('A,BB,CCC,DD')) =
        SUBSTR(ITEMS, INSTR(ITEMS, ',', 1, 1) + 1, LENGTH(ITEMS)) OR
        SUBSTR('A,BB,CCC,DD', 1, INSTR('A,BB,CCC,DD', ',', -1, 1) - 1) =
                                 --最后一个逗号-1的位置
        SUBSTR(ITEMS, 1, INSTR(ITEMS, ',', -1, 1) - 1))
;--where条件里面的'A,BB,CCC,DD'是你输入的字符串，自己替换
--主要目的是
----1、判断输入字符串和表中字符串的逗号个数 LENGTH(ITEMS) - LENGTH(REPLACE(ITEMS, ',')
-------相等则符合你的第一个要求
----2、再截取第一位到最后一个逗号之前的数据作比较
-------SUBSTR(ITEMS, 1, INSTR(ITEMS, ',', -1, 1) - 1)
----3、再截取第1个逗号之后的数据做比较
-------SUBSTR(ITEMS, INSTR(ITEMS, ',', 1, 1) + 1, LENGTH(ITEMS)) 
---测试结果
[SYS@orcl] SQL>WITH T1 AS
  2   (SELECT 1 ID, 'A,BB,CCC,DD,EE' ITEMS FROM DUAL
  3    UNION ALL
  4    SELECT 2, 'AD,DDD,CCC,DD' ITEMS FROM DUAL
  5    UNION ALL
  6    SELECT 3, 'AAA,BB,CCC,DD' ITEMS FROM DUAL
  7    UNION ALL
  8    SELECT 4, 'A,BB,CCC' ITEMS FROM DUAL
  9    UNION ALL
 10    SELECT 5, 'BB,CCC,DD,AA' ITEMS FROM DUAL)
 11  SELECT *
 12    FROM T1
 13   WHERE LENGTH('A,BB,CCC,DD') - LENGTH(REPLACE('A,BB,CCC,DD', ',')) =
 14         LENGTH(ITEMS) - LENGTH(REPLACE(ITEMS, ','))
 15     AND (SUBSTR('A,BB,CCC,DD',
 16                 INSTR('A,BB,CCC,DD', ',', 1, 1) + 1,
 17                 LENGTH('A,BB,CCC,DD')) =
 18          SUBSTR(ITEMS, INSTR(ITEMS, ',', 1, 1) + 1, LENGTH(ITEMS)) OR
 19          SUBSTR('A,BB,CCC,DD', 1, INSTR('A,BB,CCC,DD', ',', -1, 1) - 1) =
 20          SUBSTR(ITEMS, 1, INSTR(ITEMS, ',', -1, 1) - 1));        ID ITEMS
---------- --------------
         3 AAA,BB,CCC,DD

Answer 4

--可能我的理解有问题，【2.库中至少匹配其中的N-1部分（要保证相对顺序）】这个的意思如果是
----'A,BBB,CCC,DD' 和字符串'A,BB,CCC,DD' 也属于位置相对的话就用下面的SQL
----18行开始出现的字符串['A,BB,CCC,DD']你自己替换成你输入的字符串即可。
[SYS@orcl] SQL>WITH T1 AS
  2   (SELECT 1 ID, 'A,BB,CCC,DD,EE' ITEMS FROM DUAL
  3    UNION ALL
  4    SELECT 2, 'AD,DDD,CCC,DD' ITEMS FROM DUAL
  5    UNION ALL
  6    SELECT 3, 'AAA,BB,CCC,DD' ITEMS FROM DUAL
  7    UNION ALL
  8    SELECT 4, 'A,BB,CCC' ITEMS FROM DUAL
  9    UNION ALL
 10    SELECT 5, 'BB,CCC,DD,AA' ITEMS FROM DUAL
 11    UNION ALL
 12    SELECT 6, 'A,BBB,CCC,DD' ITEMS FROM DUAL
 13    UNION ALL
 14    SELECT 7, 'A,BB,C,DD' ITEMS FROM DUAL
 15    UNION ALL
 16    SELECT 8, 'A,BB,CCC,DD,EEE' ITEMS FROM DUAL
 17  )
 18  SELECT T3.ITEMS
 19    FROM (SELECT LENGTH(ITEMS) - LENGTH(REPLACE(ITEMS, ',')) LEN2,
 20                 ITEMS,
 21                 L,
 22                 REGEXP_SUBSTR(ITEMS, '[^, ]+', 1, L) AS A1
 23            FROM T1, (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100) T2
 24           WHERE LENGTH(ITEMS) - LENGTH(REPLACE(ITEMS, ',')) + 1 >= L) T3,
 25         (SELECT LENGTH('A,BB,CCC,DD') - LENGTH(REPLACE('A,BB,CCC,DD', ',')) LEN1,
 26                 LEVEL L1,
 27                 REGEXP_SUBSTR('A,BB,CCC,DD', '[^, ]+', 1, LEVEL) A1
 28            FROM DUAL
 29          CONNECT BY LEVEL <= LENGTH('A,BB,CCC,DD') -
 30                     LENGTH(REPLACE('A,BB,CCC,DD', ',')) + 1) T4
 31   WHERE T3.LEN2 = T4.LEN1
 32     AND T3.L = T4.L1
 33     AND T3.A1 = T4.A1
 34   GROUP BY T3.ITEMS
 35  HAVING COUNT (T3.ITEMS) >= 3;ITEMS
---------------
A,BBB,CCC,DD
AAA,BB,CCC,DD
A,BB,C,DD

Answer 5

补充一下：上面的sql我自己加了几条测试数据进行测试
 12    SELECT 6, 'A,BBB,CCC,DD' ITEMS FROM DUAL
 13    UNION ALL
 14    SELECT 7, 'A,BB,C,DD' ITEMS FROM DUAL
 15    UNION ALL
 16    SELECT 8, 'A,BB,CCC,DD,EEE' ITEMS FROM DUAL

Answer 6

好的，谢谢 奔驰M888，
我明天去公司试试，现在没有oracle数据库，试不了。

Answer 7

连续发了三个帖子回复不了了，有一点点缺陷，就是最后那个>=3 
写的太快疏忽了，你改成
>=LENGTH('A,BB,CCC,DD')-LENGTH(REPLACE('A,BB,CCC,DD', ',')) 
'A,BB,CCC,DD'就是你输入的字符串。 这样通用一下符合你 N-1个匹配的要求。

Answer 8

应该可以结贴了，但是还是有一个小求助。
就是一串字符
比如‘A,BD,CF,D’
怎样快速获取我想要的分割后的第几部分，
比如第三部分‘CF’呢？

Answer 9

select regexp_substr('A,BD,CF,D','[^, ]+',1,3)
  from dual;--说明：1,3 是取第三个。同理 1,2取第二个,1,1取第一个。
-------'[^, ]+' 说明分割符为[,]，同理 '[^; ]+'说明分割符为[;]。