CODEWITHSUNDEEP
sqloracle-databaseclob
Chen Xie
Chen Xie

Reputation: 4261

SQL: Whether a clob contains a number

In an Oracle database, in table A, there is a clob field called 'ID_CLOB', storing some id's from another table B.

Example:

|              ID_CLOB               |
,15,16,17,18,19,21,23,24,25,30,32,33,

And here is my question, how can I know from a SQL statement to tell if a number, say 15, is in the 'ID_CLOB' field?

Thanks in advcance.

The situation:

I am actually working on a third party application that come with this db schema. Think about the scenario: that in table B, there are person information per line, and table A, let's assume its a department table, each row is a department and the clob field is used to store the information of who are in that department.

Upvotes: 1

Views: 586

Answers (3)

Mark Wilkins
Mark Wilkins

Reputation: 41222

If the format of the data in that field is guaranteed to have comma delimiters before and after each value with no spaces, then the POSITION scalar would find it:

SELECT * FROM A
      WHERE POSITION( ',15,', in id_clob ) > 0;

This is not very efficient, though, and is fragile. If there are spaces between the values and the commas or if the first value is not preceded by a comma or if the last value is not trailed by a comma, it will fail.

As others have pointed out, it would be better (if you can) to change the database design. In the real world, though, that is not always possible.

Upvotes: 2

Randy
Randy

Reputation: 16677

i would suggest building a new table - 

person_department
-----------------
person_id
department_id

and struggle through the one time parsing of your badly formatted data and put it into this structure.

then you can query easily.

Upvotes: 1

juergen d
juergen d

Reputation: 204766

Instead of thinking of a query for your problem you should rather redesign your DB. Your structure violates the 1st normalisation form of database design.

Upvotes: 0

Related Questions