求大神·关于oracle查找字符串问题

[geminirs]

求各位帮忙看看，表中某个字段值很乱，想抽取有用的信息出来并做些计算
如：
id value
1 _id:$oid:573e5bb44d89070f00cf65e9,types:20 GP,_id:$oid:573e5bb44d89070f00cf65e8,types:40 HQ
2 _id:$oid:5758a988c74d490f0093a2bc,types:
3 qty:2,_id:$oid:573d7e214d89070f00ce5e60,types:20 GP,qty:3,_id:$oid:573d7e214d89070f00ce5e5f,types:40 HQ

--------------------------------------------------------------
想抽取的结果是
id qty* types**
1 0 20GP, 40HQ
2 0
3 5 20GP, 40HQ

*qty: 字段中所有的qty相加 （不确定有几个qty）
**types: 字段中所有types值串连 （同不确定有几个types）

[Z_B_Hrunqian]

用正则表达式去试试吧

[js14982]

select (length(value)-length(replace(value, 'qty')))/length('qty') as qty from dual; --第1个 第二个好像要用正则

[z123zjf]

分开查出qty和type，然后根据id关联

[卖水果的net]

楼主有时间研究一下 regexp_replace ，可以解决你的问题；

[chengccy]

WITH TMP AS ( SELECT 1 AS id , '_id:$oid:573e5bb44d89070f00cf65e9,types:20 GP,_id:$oid:573e5bb44d89070f00cf65e8,types:40 HQ' AS value FROM DUAL UNION ALL SELECT 2 , '_id:$oid:5758a988c74d490f0093a2bc,types:' FROM DUAL UNION ALL SELECT 3 , 'qty:2,_id:$oid:573d7e214d89070f00ce5e60,types:20 GP,qty:3,_id:$oid:573d7e214d89070f00ce5e5f,types:40 HQ' FROM DUAL) SELECT ID, SUM(NVL("qty*", 0)) AS "qty*", LISTAGG("types**", ',') WITHIN GROUP(ORDER BY ID) FROM (SELECT T.*, REPLACE(REGEXP_SUBSTR(REGEXP_SUBSTR(T.VALUE, '[^,]+', 1, COLUMN_VALUE), '^types:[^,]+', 1, 1), 'types:') AS "types**", REPLACE(REGEXP_SUBSTR(REGEXP_SUBSTR(T.VALUE, '[^,]+', 1, COLUMN_VALUE), '^qty:[^,]+', 1, 1), 'qty:') AS "qty*" FROM TMP T, TABLE(CAST(MULTISET (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT(T.VALUE, ',') + 1) AS SYS.ODCINUMBERLIST)) T2) GROUP BY ID

[chengccy]

正则水平不行,可能有更好的方法

[ghx287524027]

有时间研究一下Oracle正则表达式函数吧：regexp_like、regexp_substr、regexp_instr、regexp_replace

[桃花岛黄岛主]

WITH TMP AS
 (  
 SELECT 1 AS  id ,      '_id:$oid:573e5bb44d89070f00cf65e9,types:20 GP,_id:$oid:573e5bb44d89070f00cf65e8,types:40 HQ' AS value FROM DUAL UNION ALL 
 SELECT 2 ,    '_id:$oid:5758a988c74d490f0093a2bc,types:'  FROM DUAL UNION ALL 
 SELECT 3  ,  'qty:2,_id:$oid:573d7e214d89070f00ce5e60,types:20 GP,qty:3,_id:$oid:573d7e214d89070f00ce5e5f,types:40 HQ'  FROM DUAL)
 
 select t.*,
 
 regexp_replace(value,'[^,]*qty:([0-9]*,?)|[^,]*,?','\1') a,
 regexp_replace(value,'[^,]*types:([^,]*,?)|[^,]*,?','\1') b from tmp t

qty 这个你在外层自己写一个自定义的函数，求一下各就可以了