Join on number is contained in string

Question

In my oracle database i have 2 tables and i have to join them where in TABLE A i have column ID which is number, and TABLE B where i have column SCIDS which is varchar2. Here is some example:

TABLEA.ID | TABLEB.SCIDS
162       | 162,163
162       | 555,162,33
161       | 161

Some sql i tried

select A.ID
from TABLEA A
    left JOIN TABLEB B 
where "," || S.ID in "," || B.SCIDS

This does not work it return only the records that are not "array" in SCIDS e.g 3rd record 161.

How to return all ?

this might help : https://stackoverflow.com/questions/23276344/like-operator-in-inner-join-in-sql — dbajtr, Sep 25 '17 at 08:45
Poor table design. Never, ever store data as comma separated items - it will only cause you lots of trouble. — jarlh, Sep 25 '17 at 08:46
Refer https://stackoverflow.com/questions/18585979/sql-parse-comma-delimited-string-and-use-as-join — Kaushik Nayak, Sep 25 '17 at 08:47

Kaushik Nayak · Accepted Answer · 2017-09-25T09:26:15.113

2

You could try this, though not completely efficient one

with dist_tablea
     AS (SELECT DISTINCT ID
           FROM TABLEA)

SELECT a.ID, B.SCIDS
  FROM dist_tablea a LEFT JOIN TABLEB b ON REGEXP_LIKE (b.SCIDS, '(^|,)'||a.id||'(,|$)')

Output:

    ID  SCIDS
-------  -------
    161 161
    162 162,163
    162 555,162,33

edited Sep 25 '17 at 09:26

answered Sep 25 '17 at 09:10

Kaushik Nayak

30,772
5
32
45

This worked thank you. – xMilos Sep 25 '17 at 09:16
This does not work - it will match `ID = 16` and `SCIDS = 161,162` since it is doing sub-string matches and `16` is a sub-string of `161`. Also, using `REGEXP_LIKE` is much less efficient than just using `LIKE` (and adding in regular expressions adds no benefit here). – MT0 Sep 25 '17 at 09:18
@MT0 ah you are right – xMilos Sep 25 '17 at 09:26
@MT0 : Thanks! . Modified the pattern now. – Kaushik Nayak Sep 25 '17 at 09:27
@MasterYi : Check Now.. – Kaushik Nayak Sep 25 '17 at 09:29
@KaushikNayak it works – xMilos Sep 25 '17 at 09:33

MT0 · Answer 2 · 2017-09-25T09:32:36.793

Concatenate commas before and after both fields and then use the LIKE operator:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TABLEA( ID ) AS
  SELECT 161 FROM DUAL UNION ALL
  SELECT 162 FROM DUAL;

CREATE TABLE TABLEB( SCIDS ) AS
  SELECT '162,163' FROM DUAL UNION ALL
  SELECT '555,162,33' FROM DUAL UNION ALL
  SELECT '161' FROM DUAL;

Query 1:

SELECT *
FROM   TABLEA A
       LEFT OUTER JOIN TABLEB B
       ON ( ',' || B.SCIDS || ',' LIKE '%,' || A.ID || ',%' )

Results:

|  ID |      SCIDS |
|-----|------------|
| 161 |        161 |
| 162 |    162,163 |
| 162 | 555,162,33 |

Also:

When you use a LEFT [OUTER] JOIN you need to specify the join condition in the ON clause.
String literals are surrounded by ' single quotes. Double quotes are used (primarily) to enforce case-sensitivity on object identifiers (i.e. column names).

Join on number is contained in string

2 Answers2

Related