regex - conditional count in a list of strings in SQL Oracle

Question

I have list of several items

column1 column2                 desired col3  desired col4 
items1  car                     car           1
items2  car,car                 car           2
items3  car,toy,car             toy           1
items4  car,toy,toy             toy           2
items5  car,toy,cards           toy           1
items6  toy,cards,cards         toy           1
items7  toy,cards,toy           toy           2
items8  car,cards,toy,cards     cards         2
items9  car,cards,cards         cards         2
itrmd10 toy,cards,car           toy           1

the rule is, if there is a car item then in col3 should come car and count of cars in col4 If there is no car item or another item is present in the list then it should always display the first one that differs form car item + the count of this item.

for example

items3 there is another item as car in the list, in this case toy is the first one, so toy with count 1 will be displayed

items6 there is no car so toy as the first one is displayed

items8 there are another items as car present so the first one is cards this time and will be displayed with count of 2

items10 car needn't be the first item in the list but the rule is the same,another item in the list as car then count the first one = toy with count of 1

thanks

Answer 1

the rule is, if there is a car item then in col3 should come car and count of cars in col4 If there is no car item or another item is present in the list then it should always display the first one that differs form car item + the count of this item.

You can use:

SELECT t.*,
       REGEXP_COUNT(
         ',' || REPLACE( column2, ',', ',,' ) || ',',
         ',' || column3 || ','
       ) AS column4
FROM   (
  SELECT column1,
         column2,
         CASE
         WHEN ',' || column2 || ',' LIKE '%,car,%'
         THEN 'car'
         ELSE REGEXP_SUBSTR( column2, '[^,]+' )
         END AS column3
  FROM   table_name
) t

Which, for your sample data:

CREATE TABLE table_name ( column1, column2 ) AS
SELECT 'items1',  'car'                 FROM DUAL UNION ALL
SELECT 'items2',  'car,car'             FROM DUAL UNION ALL
SELECT 'items3',  'car,toy,car'         FROM DUAL UNION ALL
SELECT 'items4',  'car,toy,toy'         FROM DUAL UNION ALL
SELECT 'items5',  'car,toy,cards'       FROM DUAL UNION ALL
SELECT 'items6',  'toy,cards,cards'     FROM DUAL UNION ALL
SELECT 'items7',  'toy,cards,toy'       FROM DUAL UNION ALL
SELECT 'items8',  'car,cards,toy,cards' FROM DUAL UNION ALL
SELECT 'items9',  'car,cards,cards'     FROM DUAL UNION ALL
SELECT 'items10', 'toy,cards,car'       FROM DUAL;

Outputs:

COLUMN1 | COLUMN2             | COLUMN3 | COLUMN4
:------ | :------------------ | :------ | ------:
items1  | car                 | car     |       1
items2  | car,car             | car     |       2
items3  | car,toy,car         | car     |       2
items4  | car,toy,toy         | car     |       1
items5  | car,toy,cards       | car     |       1
items6  | toy,cards,cards     | toy     |       1
items7  | toy,cards,toy       | toy     |       2
items8  | car,cards,toy,cards | car     |       1
items9  | car,cards,cards     | car     |       1
items10 | toy,cards,car       | car     |       1

db<>fiddle here

---

If you want the first non-car item or else car if only car items then:

SELECT column1,
       column2,
       column3,
       REGEXP_COUNT( double_delimited, ',' || column3 || ',' ) AS column4
FROM   (
  SELECT t.*,
         CASE
         WHEN REPLACE( double_delimited, ',car,' ) IS NOT NULL
         THEN REGEXP_SUBSTR( REPLACE( double_delimited, ',car,' ), '[^,]+' )
         ELSE 'car'
         END AS column3
  FROM   (
    SELECT column1,
           column2,
           ',' || REPLACE( column2, ',', ',,' ) || ',' AS double_delimited
    FROM   table_name
  ) t
) t

Which outputs:

COLUMN1 | COLUMN2              | COLUMN3 | COLUMN4
:------ | :------------------- | :------ | ------:
items1  | car                  | car     |       1
items2  | car,car              | car     |       2
items3  | car,toy,car          | toy     |       1
items4  | car,toy,toy          | toy     |       2
items5  | car,toy,cards        | toy     |       1
items6  | toy,cards,cards      | toy     |       1
items7  | toy,cards,toy        | toy     |       2
items8  | car,cards,toy,cards  | cards   |       2
items9  | car,cards,cards      | cards   |       2
items10 | toy,cards,car        | toy     |       1

db<>fiddle here