IN Query with CLOB

Question

I have a column 'PHOTOIDS' of type CLOB which has a comma separated list of numbers. I need to query for all records which have a certain set of 'photoIds' in it. What is the best way to query THE CLOB column for a list of numbers.

I am looking for something semantically similar to

SELECT * FROM TABLE_NAME WHERE PHOTOIDS IN (1,2,3)

If it wasn't a CLOB column but a properly normalized design, you could look at this question with several ways: **[How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation)**. — ypercubeᵀᴹ, Jul 11 '13 at 14:23

score 0 · Answer 1 · answered Jul 11 '13 at 14:22

0

SELECT * 
FROM table_name 
WHERE dbms_lob.instr(photoids, '1') > 0 
  AND dbms_lob.instr(photoids, '2') > 0
  AND dmbs_lob.instr(photoids, '3') > 0;

This works as long as you have a relatively limited list of numbers that you want to search for :)

answered Jul 11 '13 at 14:22

xionutz2k

754
3
9

2

"Limited list of numbers" because '1' would match '10' and '11' and '111' and .... well no need to belabour the point. – APC Jul 11 '13 at 14:39
I meant by limited number of numbers that it wouldn't be efficient if you have check against a list of 100 different ids, for example. The issue that you noticed (1 matching 10 and 11 and 111) can be addressed depending on how the data is stored in that clob. If the IDs are sepparated by commas you can then simply make the condition 'dbms_lob.instr(photoids, '1,')>0'for example. I have just pointed @Skynet towards the right function (dbms_lob.instr) – xionutz2k Jul 11 '13 at 14:46
Well '1,' would match '11,' and '21,' and so on. – APC Jul 11 '13 at 14:49
I think you got my point. Let's assume we want to search for occurences of ID 1, then we will use (dbms_lob.instr(photoids, ',1,')>0 OR dbms_lob.instr(photoids, '1,')=1). The first half of the clause will look for occurences of ID 1 in the middle of the photoids field, while the second half will look for photoids clobs starting with 1,. As I said, it really depends on how the data is structured in that clob and my goal was to simply point @Skynet towards the right function :) – xionutz2k Jul 11 '13 at 14:53

score 0 · Answer 2 · answered Jul 11 '13 at 19:49

I would suggest that you normalize your design and have a child table to hold the photoIds..then it would be efficient and easy

If you cant do that, then using a procedure/function or dynamic-sql would do the trick, let me know if you want more info on how to do it using one these two.

IN Query with CLOB

2 Answers2