Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
5 rows affected

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


ID
NAME


1
App,Ple

2
App


SELECT
  *
FROM
  table_name
WHERE
  regexp_like(
    ',' || name || ',',
    ',' || replace('App,Ple', ',', ',|,') || ','
  )


ID
NAME


1
App,Ple

2
App

3
Ple

4
Ple,Abc

5
Abc,Ple


SELECT
  *
FROM
  table_name
WHERE
  regexp_like(
    ',' || name || ',',
    ',' || replace('foo,App,bar', ',', ',|,') || ','
  )


ID
NAME


1
App,Ple

2
App