0

My datatable looks like this.Data

The column contains data separated by a comma.

I am passing comma-separated values as parameter in the stored procedure.

I want to see the output as below

If I pass "Abc,Ple" - Rows returned would be 1,3,4,5

If I pass "Abc" - Rows returned would be 4,5

If I pass "App" - Rows returned would be 1,2,5

How can I accomplish this in Oracle? If I pass "App,Ple" - Rows returned would 1,2,3,4,5

APC
  • 144,005
  • 19
  • 170
  • 281
Chatra
  • 2,989
  • 7
  • 40
  • 73
  • 1
    Seems to be a rash of this on the various oracle forums the last couple of days. Having a comma-separated list of values in a single column is a fundamental design flaw. It violates First Normal Form, which is absolutely foundational to relational data theory. Depending on the actual business nature of the data, those comma-separated values should each be in its own column, or - and this is more likely - in their own rows of a child table. When your tables are properly normalized to Third Normal Form, your problem becomes trivial. – EdStevens Mar 21 '21 at 20:11
  • I agree with you that it violates the fundamental design flaw. However, this is a very old database and don't want to want to move data to new table. However, comma separated columns wont be big. It contains 4 or 5 commas at max – Chatra Mar 21 '21 at 20:40
  • You say it is an old database. What version is it? There are plenty of different ways to tokenize an CSV string in Oracle, but the range of options deflates in older versions of the product. – APC Mar 22 '21 at 11:41

2 Answers2

0

You can use:

SELECT *
FROM   table_name t
WHERE  EXISTS (
  WITH input ( value ) AS (
    SELECT 'App' FROM DUAL -- Change this to your input value
  )
  SELECT 1
  FROM   input
  WHERE  ','||t.name||',' LIKE '%,' || REGEXP_SUBSTR( value, '[^,]+', 1, LEVEL ) || ',%'
  CONNECT BY LEVEL <= REGEXP_COUNT( value, '[^,]+' )
)

Which, for the sample data:

CREATE TABLE table_name ( id, name ) AS
SELECT 1, 'App,Ple' FROM DUAL UNION ALL
SELECT 2, 'App' FROM DUAL UNION ALL
SELECT 3, 'Ple' FROM DUAL UNION ALL
SELECT 4, 'Ple,Abc' FROM DUAL UNION ALL
SELECT 5, 'Abc,Ple' FROM DUAL;

Outputs:

ID | NAME   
-: | :------
 1 | App,Ple
 2 | App

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
0

If you make use of the commas, you can "cheat".

For example, when searching for just one value, make sure every item, even the first and last, have a comma before and after...

  • ',App,Ple,' LIKE '%,App,%'

Which gives this...

SELECT
  *
FROM
  yourTable
WHERE
  ',' || Name || ',' LIKE '%,App,%'

Then you can go one step further to use regexp_like() to match against one of several items...

  • regexp_like(',App,Ple,', ',Foo,|,App,|,Bar,')

Which gives...

SELECT
  *
FROM
  yourTable
WHERE
  regexp_like(
    ',' || Name || ',',
    ',' || replace('App,Ple', ',', ',|,') || ','
  )

(please excuse typos, I did that on my phone)

Making use of @MT0 fiddle... https://dbfiddle.uk/?rdbms=oracle_18&fiddle=291567f2a9df3b409126a5ae7b3c5441

MatBailie
  • 83,401
  • 18
  • 103
  • 137