CODEWITHSUNDEEP
sqloracle-database
Jestem_z_Kozanowa
Jestem_z_Kozanowa

Reputation: 637

Query with conditions on multiple value column

I am building report in Oracle Apex 4.2. Table that report is build on has multiple values inside one of the columns. 

-----------------------------------
| ID   |  NAME   |  PROJECT_ID    |
-----------------------------------
|  1   |   P1    | 23:45:56       |
|  2   |   P2    |    23          |
|  3   |   P3    |    45:65       |
-----------------------------------

I would like to build a query to retrieve names based on project_id's.

 Select name from table where project_id = 23;

This obviously will return P2 only however I would like to build a query which would return P1 and P2 if we searched for 23.

Any help greatly appreciated.

Upvotes: 0

Views: 115

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

This is a common design in Apex due to its builtin support for colon-delimited strings (e.g. to drive shuttle controls and other item types).

I generally use this pattern:

Select name from table where INSTR(':'||project_id||':',':23:') > 0;

P.S. It's a pity about that column name - I would have called it something like PROJECT_ID_LIST.

Upvotes: 1

Hart CO
Hart CO

Reputation: 34774

You can use LIKE instead of = :

Select name from table where project_id LIKE '%23%';

If you've got a common delimiter such as the ':' in your example you could use the following to exclude results like '123':

SELECT name FROM table WHERE ':' || project_id || ':' LIKE '%:23:%'

By concatenating the delimiter to the front and back of the string, you don't have to write multiple criteria: LIKE '23:%' OR LIKE '%:23:%' OR LIKE '%:23' to handle the first and last number in the list.

Upvotes: 5

Related Questions