CODEWITHSUNDEEP
sqloracle-database
Mariners
Mariners

Reputation: 509

how to split a comma seperated field in oracle?

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

Upvotes: 0

Views: 536

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35613

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

Upvotes: 0

jbutler483
jbutler483

Reputation: 24559

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
;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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.

Upvotes: 2

Related Questions