how to split a comma seperated field in oracle?

Question

I have a table with below contents :

SQL> select RULE_IDS ||' | '|| ID ||' | '|| ALERT_COUNT from alarms ;

RULE_IDS||'|'||ID||'|'||ALERT_COUNT
--------------------------------------------------------------------------------
3714,3715,3703 | 1031 | 3
3703,3714,3722,3721 | 1032 | 4
3715 | 1033 | 1
3721,3722 | 1034 | 2
3714,3715 | 1035 | 2
3706 | 1030 | 1
3723,3714 | 1036 | 2
3703 | 1025 | 1`

My requirement is to find the count of each RULE_IDS (the comma separated values). e.g. the output should be like this :

SQL> select RULE_IDS ||' | '|| ID ||' | '|| ALERT_COUNT from alarms ;

RULE_IDS||'|'||COUNT
--------------------------------------------------------------------------------
3714 | 4
3715 | 3
3703 | 3
3721 | 2
3722 | 2
3723 | 1
3706 | 1

How can I achive that. Please help me out. Thanks in advance.

Regards

length of string with commas minus length of string without commas — Paul Maxwell, Aug 06 '14 at 13:11
possible duplicate of [Split comma seperated values of a column in row, through Oracle SQL query](http://stackoverflow.com/questions/23649813/split-comma-seperated-values-of-a-column-in-row-through-oracle-sql-query) — Allan, Aug 06 '14 at 13:26

score 2 · Accepted Answer · answered Aug 06 '14 at 12:55

2

Let me assume that you have a table of ruleids. If so, you can do this using a join:

select r.rule_id, count(*)
from alarms a join
     rules r
     on ',' || a.rule_ids || ',' like '%,' || r.rule_id || ',%'
group by r.rule_id;

Storing lists of ids as a comma delimited string is a bad idea. For one thing, you are storing integer values as a string. More importantly, SQL has a very nice structure for storing lists. It is called a table. In this case, you would want a junctions table AlertRules with one row per alert and rule.

answered Aug 06 '14 at 12:55

Gordon Linoff

1,242,037
58
646
786

Hello Gordon, I am relatively new to SQL. Could you please give me an explanation of the join that you did. – Mariners Aug 06 '14 at 13:26
@Mariners . . . It is hard to explain beyond the query uses `like` to find an element in a list and putting delimiters around each value so `10` doesn't match `1010`. – Gordon Linoff Aug 06 '14 at 14:57

score 0 · Answer 2 · answered Aug 06 '14 at 12:55

Found here: https://community.oracle.com/thread/2348338

Use REGEXP_SUBSTR:

SELECT  REGEXP_SUBSTR (str, '[^,]+', 1, 1)    AS part_1
,       REGEXP_SUBSTR (str, '[^,]+', 1, 2)    AS part_2
,       REGEXP_SUBSTR (str, '[^,]+', 1, 3)    AS part_3
,       REGEXP_SUBSTR (str, '[^,]+', 1, 4)    AS part_4
FROM    table_x
;

Can str contain emply items? For example, can you have a string like 'foo,,,bar', where you'd want to count part_2 and part_3 as NULL, and 'bar' is part_4? If so:

SELECT  RTRIM (REGEXP_SUBSTR (str, '[^,]*,', 1, 1), ',')    AS part_1
,       RTRIM (REGEXP_SUBSTR (str, '[^,]*,', 1, 2), ',')    AS part_2
,       RTRIM (REGEXP_SUBSTR (str, '[^,]*,', 1, 3), ',')    AS part_3
,       LTRIM (REGEXP_SUBSTR (str, ',[^,]*', 1, 3), ',')    AS part_4
FROM    table_x
;

score 0 · Answer 3 · answered Aug 06 '14 at 13:20

0

select
      coalesce(substr(RULE_IDS,1,instr(RULE_IDS,',')-1),RULE_IDS) as RULE_IDS
    , length(RULE_IDS) - length(replace(RULE_IDS,',','')) + 1     as num_of
from Table1

answered Aug 06 '14 at 13:20

Paul Maxwell

33,002
3
32
51

how to split a comma seperated field in oracle?

3 Answers3