How to match the chracters with # in oracle query

Question

Incident Table

I have Incident Table like below i need to match the client list in client table. How can i do that in oracle ?

INCIDENT_ID |CLIENTS_LIST     |
------------|-----------------|
56          |A001##A05M##A0AS |

Client Table

BO_NAME               |COMPANYID
----------------------|---------
Test1                 |A001     
Test2                 |A0AS     
Test3                 |A05M      
Test4                 |A0BT

Im trying to match the companyid with clients_list but there is no result.

Tried Query

 SELECT DISTINCT INCIDENT_ID,
                 CLIENTS_LIST,
                 REPLACE(CLIENTS_LIST, '##', ',') AS client_id,
                 cl.BO_NAME,
                 COMPANYID
   FROM incident ir 
   INNER JOIN Client cl
     ON  cl.companyid IN (REPLACE(CLIENTS_LIST, '##', ','))

Expected Output

BO_NAME               |COMPANYID
----------------------|---------
Test1                 |A001     
Test2                 |A0AS     
Test3                 |A05M

I suspect that if you execute `SELECT REPLACE(CLIENTS_LIST, '##', ',') FROM INCIDENT` you don't get back values which match CLIENT.COMPANYID. Oracle will not magically parse strings and break them up, whether it's by commas or pound signs or whatever. You might want to look at [this question and its highest-ranked answer](http://stackoverflow.com/questions/28677070/split-function-in-oracle-to-comma-separated-values-with-automatic-sequence). Best of luck. — Bob Jarvis - Слава Україні, Aug 26 '16 at 11:23

Rumpelstinsk · Answer 1 · 2016-08-26T11:45:57.293

2

I see a design problem there, you should always save each value in a separate column, or in a separate table with a 1-to-many relationship.

Now, you aren't going to make an efficient query, or at least, as efficient as it could be. With that in mind, you could use LIKE Operator in combination with CROSS JOIN This query is very unnefficient, but it should work:

SELECT *
FROM incidentTable t, clientTable c
WHERE t.IncidentId = 56 AND '#' || t.ClientList || '#' LIKE '%#' || c.CompanyId || '#%'

edited Aug 26 '16 at 11:45

answered Aug 26 '16 at 11:17

Rumpelstinsk

3,107
3
30
57

can u please give me the query how i need to run – Question User Aug 26 '16 at 11:29
2

+1: but note that this is predicated on a consistent naming strategy for companyIds. If its possible that you might have companyIds where the prefix of one is the value of another (e.g. A10 and A101) then this would need to be `AND '#' || t.ClientList || '#' LIKE '%#' || c.CompanyId || '#%'` (yes, concatentation operator is correct) – symcbean Aug 26 '16 at 11:30
im not clear with one. can u please join 2 tables in this query – Question User Aug 26 '16 at 11:32
@symcbean Yes, you are right, i'm gone modify my answer – Rumpelstinsk Aug 26 '16 at 11:38
@QuestionUser see the link about `cross join` I put on my answer, `CROSS JOIN` (wich is used on my query because of the `,` in the `from` clause) joins each record on the first table with all the record in the second table. – Rumpelstinsk Aug 26 '16 at 11:43
Don't worry too much about "cross" join vs. other kinds. Every join begins with a cross join, then conditions may be evaluated. What you have here is really not a cross join, it is an inner join, and it would indeed be better written in the ANSI SQL Standard syntax: `from t inner join c ON ... like ...` The `ON ...` condition doesn't have to be an equality condition; that is the most common (so it has a special name, an equijoin), but it can be any condition that compares values from the two tables. – Aug 26 '16 at 13:19

score -1 · Answer 2 · answered Aug 26 '16 at 11:12

you can use oracle instr

with inc(INCIDENT_ID,CLIENTS_LIST) as (select 56, 'A001##A05M##A0AS' from dual),
     Client(BO_NAME,COMPANYID) as 
     (select 'Test1','A001' from dual union all
      select 'Test2','A0AS' from dual union all
      select 'Test3','A05M' from dual union all
      select 'Test4','A0BT' from dual )

select * 
  from inc, client
 where instr(clients_list,companyid) > 0


INCIDENT_ID CLIENTS_LIST     BO_NAME COMPANYID
----------- ---------------- ------- ---------
         56 A001##A05M##A0AS Test1   A001      
         56 A001##A05M##A0AS Test2   A0AS      
         56 A001##A05M##A0AS Test3   A05M

How to match the chracters with # in oracle query

2 Answers2