CODEWITHSUNDEEP
sqloracle-databasesplit
Muhammad Asim
Muhammad Asim

Reputation: 159

How to get maximum COUNT of comma separated string in Oracle?

I have a table that holds data something like this in the table.

MANUFACTURER_PART_NUMBER|MANUFACTURER_NAME        |TEST_PLAN           |ATTACHED_SUPPLIER_DOCUMENT |ATTACHED_LOGITECH_REPORT|
------------------------|-------------------------|--------------------|---------------------------|------------------------|
001059-0000             |CHENGDA                  |268452,268453       |268456,268457,268459,268460|268465                  |
001059-0000             |SHANGHAI MARRISON CO.,LTD|                    |268458,268462              |                        |
001059-0000             |SUZHOU SHARETECH         |                    |                           |                        |
001059-0000             |SYSTRON                  |268451,268452,268453|268456,268457,268459,268460|268465,268466           |

I want to get the maximum count of comma separated column. For example TEST_PLAN has maximum 3 values in 4th row but ATTACHED_SUPPLIER_DOCUMENT has 4 values so I want the output to return 4 as maximum count.

I have tried this query to find out the count of these column values.

WITH CTE AS(
SELECT DISTINCT 
MP.PART_NUMBER MANUFACTURER_PART_NUMBER, M.NAME MANUFACTURER_NAME, 
RTRIM(LTRIM(PG3.MULTILIST31, ','), ',') TEST_PLAN, regexp_count(MULTILIST31, ',') + 1 AS COL1,
RTRIM(LTRIM(PG3.MULTILIST32, ','), ',') ATTACHED_SUPPLIER_DOCUMENT, regexp_count(MULTILIST32, ',') + 1 AS COL2,
RTRIM(LTRIM(PG3.MULTILIST33, ','), ',') ATTACHED_LOGITECH_REPORT, regexp_count(MULTILIST33, ',') + 1 AS COL3 
FROM MANU_PARTS MP
INNER JOIN MANUFACTURERS M ON M.ID = MP.MANU_ID 
LEFT JOIN PAGE_TWO PG2 ON PG2.ID = MP.ID
LEFT JOIN PAGE_THREE PG3 ON PG3.ID = MP.ID

WHERE PART_NUMBER = '001059-0000'
)
SELECT * FROM CTE

The output I am looking for is something like this..

MANUFACTURER_PART_NUMBER|MANUFACTURER_NAME        |TEST_PLAN           |ATTACHED_SUPPLIER_DOCUMENT |ATTACHED_LOGITECH_REPORT|MAXCOUNT|
------------------------|-------------------------|--------------------|---------------------------|------------------------|--------|
001059-0000             |SYSTRON                  |268451,268452,268453|268456,268457,268459,268460|268465,268466           |       4|
001059-0000             |CHENGDA                  |268452,268453       |268456,268457,268459,268460|268465                  |       4|
001059-0000             |SHANGHAI MARRISON CO.,LTD|                    |268458,268462              |                        |       4|
001059-0000             |SUZHOU SHARETECH         |                    |                           |                        |       4|

Upvotes: 0

Views: 516

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Use REGEXP_COUNT to count commas. Use GREATEST to get the maximum count:

select
  greatest(
    regexp_count(test_plan, ','),
    regexp_count(attached_supplier_document, ','),
    regexp_count(attached_logitech_report, ',')
  ) + 1
from mytable;

If you want the maximum over all rows, use MAX:

select
  max(
    greatest(
      regexp_count(test_plan, ','),
      regexp_count(attached_supplier_document, ','),
      regexp_count(attached_logitech_report, ',')
    ) + 1
  )
from mytable;

IF you want the maximum count to be shown with each row, use MAX(...) OVER () instead of MAX(...).

(This gives a count of 1, if there is no value at all in a column. You can avoid this with a CASE expression, but I imagine that this is may not even be necessary, as there may always be a value in at least one of the columns.)

EDIT: (by Gordon)

You may need COALESCE() if any of the values are NULL:

select
  greatest(
    coalesce(regexp_count(test_plan, ','), 0),
    coalesce(regexp_count(attached_supplier_document, ','), 0),
    coalesce(regexp_count(attached_logitech_report, ','), 0)
  ) + 1
from mytable;

Upvotes: 3

MT0
MT0

Reputation: 167962

You don't need regular expressions and could instead, remove the commas and compare the length of the strings before and after replacement and add 1 to get the number of items. You can then use GREATEST to find the maximum over the columns and an analytic MAX function to find it over all the rows:

SELECT t.*,
       MAX(
         GREATEST(
           COALESCE(
             LENGTH( test_plan )
             - LENGTH( REPLACE( test_plan, ',' ) )
             + 1,
             0
           ),
           COALESCE(
             LENGTH( attached_supplier_document )
             - LENGTH( REPLACE( attached_supplier_document, ',' ) )
             + 1,
             0
           ),
           COALESCE(
             LENGTH( attached_logitech_report )
             - LENGTH( REPLACE( attached_logitech_report, ',' ) )
             + 1,
             0
           )
         )
       ) OVER ( PARTITION BY manufacturer_part_number )
         AS maxcount
FROM   table_name t

(Remove the PARTITION BY manufacturer_part_number so you have MAX( ... ) OVER () if you want to find the maximum over all rows rather than per each part number.)

So for your test data:

CREATE TABLE table_name (
       MANUFACTURER_PART_NUMBER, MANUFACTURER_NAME,         TEST_PLAN,             ATTACHED_SUPPLIER_DOCUMENT,   ATTACHED_LOGITECH_REPORT
) AS
SELECT '001059-0000',           'CHENGDA',                  '268452,268453',       '268456,268457,268459,268460','268465'        FROM DUAL UNION ALL
SELECT '001059-0000',           'SHANGHAI MARRISON CO.,LTD',NULL,                  '268458,268462',              NULL            FROM DUAL UNION ALL
SELECT '001059-0000',           'SUZHOU SHARETECH',         NULL,                  NULL,                         NULL            FROM DUAL UNION ALL
SELECT '001059-0000',           'SYSTRON',                  '268451,268452,268453','268456,268457,268459,268460','268465,268466' FROM DUAL;

This outputs:

MANUFACTURER_PART_NUMBER | MANUFACTURER_NAME         | TEST_PLAN            | ATTACHED_SUPPLIER_DOCUMENT  | ATTACHED_LOGITECH_REPORT | MAXCOUNT
:----------------------- | :------------------------ | :------------------- | :-------------------------- | :----------------------- | -------:
001059-0000              | CHENGDA                   | 268452,268453        | 268456,268457,268459,268460 | 268465                   |        4
001059-0000              | SYSTRON                   | 268451,268452,268453 | 268456,268457,268459,268460 | 268465,268466            |        4
001059-0000              | SUZHOU SHARETECH          | null                 | null                        | null                     |        4
001059-0000              | SHANGHAI MARRISON CO.,LTD | null                 | 268458,268462               | null                     |        4

db<>fiddle here

Upvotes: 0

Related Questions