Thanks for the question, Amey .
Asked: November 15, 2017 - 4:23 pm UTC
Last updated: November 16, 2017 - 12:57 pm UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi,
Struggling for sometime now.
I have a list read from a table column - (1,2,3,10,11)
I need to check if my number is present in the above list
So for 1 INSTR(1, List) return 2 which is fine.
Now the issue is if the list contain (2,3,10,11) and if i again run the INSTR(1,list) it retuns an occurence which is for number 10 which I dont want.
I used '1,' as the search but then it returns occurrence due to 11, for (3,6,10,11,12)
Can someone help around this. If there is a way to do exact searcH ?
and Chris said...
One way around this is to use regular expressions.
Search for your value:
- preceded by a comma or the start of line (^)
- followed by a comma or the end of line ($)
with rws as (
select '1,2,3,10,11,12' str from dual union all
select '2,3,10,11' str from dual union all
select '2,3,10,11,12' str from dual
)
select * from rws
where regexp_instr(str, '(^|,)1(,|$)') > 0;
STR
1,2,3,10,11,12
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment