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

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.

CodePudding user response：

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>

CodePudding user response：

WHERE (',' allow_zones ',') LIKE ('%,' search_for_zone ',%')

CodePudding user response：

Rather slow, but working method is to use LIKE:

SELECT * FROM mytable
WHERE allow_zones LIKE '#%'