Oracle parsing string for separators

Question

I have one table in Oracle 11g database

I am storing some value like '|' separated mode as below

Table: ABC
FIELD: XYZ

Values :

9
1|12
52
5|112

Now I want to find ID from those values

I used REGEXP_LIKE but it will get all the rows containing value

Suppose I will search for 5 then it will give result for '5' and also for '52'

please help me to search particulate id from this field

Thanks

Answer 1

Try this.

select * from table_name
 where '|' || column_name || '|' like '%|' || 'search_string' || '|%';

Sample here.