How to do like between two strings separated by , in oracle

Question

I have values in database as string stored as 1234,5678 how can i do get list of all values which have any one of the value in database

if i do select * from students where room_number like '1234,5678';

it give empty only values which have both numbers how to get all which belongs to '1234' '5678' and '1234,5678'

You should change the way the data is stored in the database. Please see this article: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Andy Lester, May 20 '22 at 01:49

score 0 · Answer 1 · answered May 20 '22 at 06:35

The way you described it, you'll have to split comma-separated values into rows.

I'm not sure what you mean by saying that someone's room number can really be 1234,5678 but hey - you probably do; that's what the UNION ALL part of the query does.

select *
from students s
where s.room_number in (-- split string into rows, i.e. separate '1234,5678' to
                        -- '1234' and '5678'
                        select regexp_substr(t.string, '[^,]+', 1, column_value)
                        from other_table t cross join
                          table(cast(multiset(select level from dual
                                              connect by level <= regexp_count(t.string, ',') + 1
                                             ) as sys.odcinumberlist))
                        union all
                        -- this is a value you have in that other table "as is" 
                        -- (i.e. '1234,5678')
                        select t.string
                        from other_table t                                             
                       );

MT0 · Answer 2 · 2022-05-20T08:27:17.333

You do not need to split the list into separate terms.

You can use LIKE and surround the list and the matching values with the list delimiter:

SELECT *
FROM   students
WHERE  ',' || :yourList || ',' LIKE '%,' || room_number || ',%';

Which for your (hard-coded) values would be:

SELECT *
FROM   students
WHERE  ',1234,5678,' LIKE '%,' || room_number || ',%';

If you have the values stored in another table then you can JOIN based on LIKE:

SELECT s.*
FROM   students s
       INNER JOIN other_table o
       ON (',' || o.room_list || ',' LIKE '%,' || s.room_number || ',%');

Which, for the sample data:

CREATE TABLE students (id, room_number) AS
SELECT 1, '1234' FROM DUAL UNION ALL
SELECT 2, '5678' FROM DUAL UNION ALL
SELECT 3, '1234,5678' FROM DUAL UNION ALL
SELECT 4, '9999' FROM DUAL;

CREATE TABLE other_table (room_list) AS
SELECT '1234,5678' FROM DUAL;

Outputs:

ID ROOM_NUMBER

1 1234

2 5678

3 1234,5678

db<>fiddle here

How to do like between two strings separated by , in oracle

2 Answers2