Search for a string in comma separated string column in oracle database?

Question

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.

Does this answer your question? [Is it possible to query a comma separated column for a specific value?](https://stackoverflow.com/questions/7212282/is-it-possible-to-query-a-comma-separated-column-for-a-specific-value) — astentx, Aug 08 '22 at 07:02

score 3 · Accepted Answer · answered Aug 08 '22 at 05:41

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>

Thanks alot, the answers works fine. But could you tell me which one should i prefer the first one or the second one. Which one is efficient and fast? — Roshan Twanabasu, Aug 08 '22 at 06:00
Any would do on a small data set. If you have to work with *a lot of data*, then regular expressions are getting slower and slower. In that case, seriously consider changing data model: create a *detail* table which contains allow_zones with a foreign key to your current table. Then you'd avoid inefficient search (like the above) and use JOIN instead, which - on properly indexed tables - should run way faster. — Littlefoot, Aug 08 '22 at 06:09

score 0 · Answer 2 · answered Jul 27 '23 at 18:41

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.

Juan C. · Answer 3 · 2023-07-27T17:46:30.227

-1

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

edited Jul 27 '23 at 17:46

answered Aug 08 '22 at 05:47

Juan C.

84
6

The above query wont help me cause if i have to search for string ```1```, then it would give me all the instance where there's ```1``` in it. Like ``111`` and other string containing 1's. – Roshan Twanabasu Aug 08 '22 at 06:01
you are right; I fixed that problem in the eddited answer – Juan C. Jul 27 '23 at 17:43

Search for a string in comma separated string column in oracle database?

3 Answers3