I have a comma separated string column(allow_zones) in my oracle db like following:
--------------------------
| id | name | allow_zones|
| 1 | test | 1,23,44,67 |
| 2 | user | 3,33,4,97 |
| 3 | sam | 9,77,34,6 |
| 4 | kate | 2,83,49,69 |
--------------------------
Now i want to check whether a string suppose 23 is present on column allow_zones.
I donot seem to know how to do this.
A simple example might be this:
Sample data:
SQL> with test (id, name, allow_zones) as
2 (select 1, 'test', '1,23,44, 67' from dual union all
3 select 2, 'user', '3,33,4, 97' from dual union all
4 select 5, 'mike', '1234,5,6' from dual union all
5 select 6, 'tige', '23,52,4' from dual union all
6 select 7, 'scot', '0,15,123' from dual union all
7 select 8, 'king', '124,23' from dual
8 )
Fetch rows that contain ,23, (if not literally, then fabricated):
9 select *
10 from test
11 where ',' || allow_zones ||',' like '%,' || 23 || ',%';
ID NAME ALLOW_ZONES
---------- ---- -----------
1 test 1,23,44, 67
6 tige 23,52,4
8 king 124,23
Or, you could
<snip>
9 -- first split allow_zones into rows ...
10 temp as
11 (select regexp_substr(allow_zones, '[^,]+', 1, column_value) val,
12 id, name, allow_zones
13 from test cross join
14 table(cast(multiset(select level from dual
15 connect by level <= regexp_count(allow_zones, ',') + 1
16 ) as sys.odcinumberlist))
17 )
18 -- ... then fetch those that contain 23
19 select id, name, allow_zones
20 from temp
21 where val = '23';
ID NAME ALLOW_ZONES
---------- ---- -----------
1 test 1,23,44, 67
6 tige 23,52,4
8 king 124,23
SQL>
Check for your target value where preceded by the start of the string or a comma, and proceeded by a comma or the end of the string.
with test (id, name, allow_zones) as (
select 1, 'test', '1,23,44, 67' from dual union all
select 2, 'user', '3,33,4, 97' from dual union all
select 5, 'mike', '1234,5,6' from dual union all
select 6, 'tige', '23,52,4' from dual union all
select 7, 'scot', '0,15,123' from dual union all
select 8, 'king', '124,23' from dual
)
select *
from test
where regexp_like(allow_zones, '(^|,)23(,|$)');
ID NAME ALLOW_ZONES
---------- ---- -----------
1 test 1,23,44, 67
6 tige 23,52,4
8 king 124,23
3 rows selected.
You can surround the column name in the WHERE statement with ',' using ||, so that commas will be added to the begining and end of the value of each row, and then you could match them against a LIKE statement with commas, in that way you will match "23" "23," ",23" and ",23,":
SELECT * FROM mytable
WHERE ',' || allow_zones ||',' LIKE '%,23,%'
Alternatively, you could also use a regex in your search, but that query would likely be slower
1, then it would give me all the instance where there's 1 in it. Like 111 and other string containing 1's.