Given the existing data model, here are a couple of slightly fragile approaches:

-- T is a dummy table for demonstration
with T as (
select 'X' as col1,     'AB 1.0,CD 1.0,CD 2.0' as col2 from dual
union
select 'Y' as col1,     'AE 2.0,CD 3.0' as col2 from dual
)
select col1 from T where ','||col2||',' like '%,'||'CD 1.0'||',%'
/

In this example, we add delimiters to both sides of the list column and use a like clause. This has a couple of issues:

• We are depending on the search text not containing the delimiter
• We need to apply a function to col2, and the LIKE clause contains a leading wildcard, preventing us from using indexes.

with T as (
select 'X' as col1,     'AB 1.0,CD 1.0,CD 2.0' as col2 from dual
union
select 'Y' as col1,     'AE 2.0,CD 3.0' as col2 from dual
)
select col1 from T where regexp_like(col2, '^(.*,)?'||'CD 3.0' || '(,.*)?$')
/

This one is a little bit more formal in that it uses a regular expression.

• The function still needs to be applied to all rows being evaluated.
• This would have issues with search text that breaks regular expression syntax.
• The regular expression will be more flexible than the LIKE approach

As others have mentioned, the table design is not optimal; nevertheless, there are times when we have to work with a an existing production database that cannot be changed, possibly provided by a vendor. We do the best we can to work with what we have.

In both cases, the search text should be groomed so that it does not have embedded delimiters (e.g. ‘%’ or ‘,’) and possibly to remove leading and trailing whitespace.

You can tokenize the target string with classical regexp_substr and connect by or with XPATH expression, you have the choice to put the condition in the XPATH or in the outer WHERE clause:

select col1, val
from 
data d,
xmltable( 
  'for $i in (ora:tokenize($X,",")) return $i'
  passing col2 as "X"
  
  columns 
    val varchar2(4000) path '.'
) tb
where val = 'CD 3.0'
;

select col1, val
from 
data d,
xmltable( 
  'for $i in (ora:tokenize($X,",")) where ($target = $i) return $i'
  passing col2 as "X", 'CD 3.0' as "target"
  
  columns 
    val varchar2(4000) path '.'
) tb
;

And you can even get the index of the token if you want:

select col1, val
from 
data d,
xmltable( 
  'for $i at $idx in (ora:tokenize($X,",")) where ($target = $i) return ($idx, $i)'
  passing col2 as "X", 'CD 3.0' as "target"
  
  columns 
    val varchar2(4000) path '.'
) tb
;