CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle-sqldeveloper
Roshan Twanabasu
Roshan Twanabasu

Reputation: 198

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

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.

Upvotes: 1

Views: 1815

Answers (3)

Gary_W
Gary_W

Reputation: 10360

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.

Upvotes: 0

Juan C.
Juan C.

Reputation: 84

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

Upvotes: -1

Littlefoot
Littlefoot

Reputation: 142720

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>

Upvotes: 3

Related Questions