Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t1
as
select 1 as ID_T1, 'ASCORBIC ACID, PARACETAMOL, POTASSIUM HYDROGEN CARBONATE' as NAME1 from dual union all
select 2, 'SODIUM HYDROGEN CARBONATE, SODIUM CARBONATE ANHYDROUS, CITRIC ACID' from dual union all
select 3, 'CAFFEINE, PARACETAMOL PH. EUR.' from dual union all
select 4, 'PSEUDOEPHEDRINE HYDROCHLORIDE,DEXCHLORPHENIRAMINE MALEATE' from dual union all
select 100, 'PARACETAMOL, DEXTROMETHORPHAN, PSEUDOEPHEDRINE, PYRILAMINE' from dual union all -- additional row 
select 110, 'sodium hydroxide, potassium carbonate' from dual -- additional row 
;
6 rows affected

create table t2 
as
select 4 as ID_T2, 'POTASSIUM HYDROGEN CARBONATE, ASCORBIC ACID, PARACETAMOL' as NAME2 from dual union all
select 5, 'SODIUM HYDROGEN CARBONATE, SODIUM CARBONATE ANHYDROUS' from dual union all
select 6, 'PARACETAMOL PH. EUR.,CAFFEINE' from dual union all
select 7, 'CODEINE PHOSPHATE, PARACETAMOL DC' from dual union all
select 8, 'DEXCHLORPHENIRAMINE MALEATE, DEXTROMETHORPHAN HYDROBROMIDE' from dual union all
select 10, 'DEXCHLORPHENIRAMINE MALEATE, PSEUDOEPHEDRINE HYDROCHLORIDE' from dual union all
select 200, 'PARACETAMOL, DEXTROMETHORPHAN, PSEUDOEPHEDRINE, PYRILAMINE1' from dual union all -- additional row
select 210, 'sodium hydroxide, potassium carbonate' from dual union all -- additional row   
select 211, 'potassium hydroxide, sodium carbonate' from dual -- additional row (chemical compounds different!)
;
9 rows affected

create table tokens
as 
select
  ltrim(        -- ltrim() and rtrim() remove leading/trailing spaces (blanks)
    rtrim( 
      substr( N.wrapped
      , instr( N.wrapped, ',', 1, T.pos ) + 1
      , instr( N.wrapped, ',', 1, T.pos + 1 ) - instr( N.wrapped, ',', 1, T.pos ) - 1 
      ) 
    )
  ) token
, N.id
from (        
  select ',' || name1 || ',' as wrapped, 'T1_' || to_char( id_t1 ) as id from t1 -- names wrapped in commas, (table)_id
  union all
  select ',' || name2 || ',' , 'T2_' || to_char( id_t2 ) from t2  
) N join (  
  select level as pos   -- (max) possible position of char in existing token
  from dual 
  connect by level <= (
    select greatest(    -- find the longest string ie max position (query T1 and T2) 
      ( select max( length( name1 ) ) from t1 )
    , ( select max( length( name2 ) ) from t2 )
    ) as pos
    from dual
  )  
) T
  on T.pos <= ( length( N.wrapped ) - length( replace( N.wrapped, ',') ) ) - 1 
;
37 rows affected

select * from tokens ;


TOKEN
ID


ASCORBIC ACID
T1_1

SODIUM HYDROGEN CARBONATE
T1_2

CAFFEINE
T1_3

PSEUDOEPHEDRINE HYDROCHLORIDE
T1_4

PARACETAMOL
T1_100

sodium hydroxide
T1_110

POTASSIUM HYDROGEN CARBONATE
T2_4

SODIUM HYDROGEN CARBONATE
T2_5

PARACETAMOL PH. EUR.
T2_6

CODEINE PHOSPHATE
T2_7

DEXCHLORPHENIRAMINE MALEATE
T2_8

DEXCHLORPHENIRAMINE MALEATE
T2_10

PARACETAMOL
T2_200

sodium hydroxide
T2_210

potassium hydroxide
T2_211

PARACETAMOL
T1_1

SODIUM CARBONATE ANHYDROUS
T1_2

PARACETAMOL PH. EUR.
T1_3

DEXCHLORPHENIRAMINE MALEATE
T1_4

DEXTROMETHORPHAN
T1_100

potassium carbonate
T1_110

ASCORBIC ACID
T2_4

SODIUM CARBONATE ANHYDROUS
T2_5

CAFFEINE
T2_6

PARACETAMOL DC
T2_7

DEXTROMETHORPHAN HYDROBROMIDE
T2_8

PSEUDOEPHEDRINE HYDROCHLORIDE
T2_10

DEXTROMETHORPHAN
T2_200

potassium carbonate
T2_210

sodium carbonate
T2_211

POTASSIUM HYDROGEN CARBONATE
T1_1

CITRIC ACID
T1_2

PSEUDOEPHEDRINE
T1_100

PARACETAMOL
T2_4

PSEUDOEPHEDRINE
T2_200

PYRILAMINE
T1_100

PYRILAMINE1
T2_200


select
  S1.id id1
, S2.id id2
, S1.tokengroup_T1
, S2.tokengroup_T2
from 
(
  select substr( id, 4, length( id ) - 3 ) id
  , listagg( token, ' + ' ) within group ( order by token ) tokengroup_T1
  from tokens
  group by id 
  having substr( id, 1, 3 ) = 'T1_'
) S1 
  join 
(
  select substr( id, 4, length( id ) - 3 ) id
  , listagg( token, ' + ' ) within group ( order by token ) tokengroup_T2
  from tokens
  group by id 
  having substr( id, 1, 3 ) = 'T2_'
) S2 
  on S1.tokengroup_T1 = S2.tokengroup_T2
;


ID1
ID2
TOKENGROUP_T1
TOKENGROUP_T2


4
10
DEXCHLORPHENIRAMINE MALEATE + PSEUDOEPHEDRINE HYDROCHLORIDE
DEXCHLORPHENIRAMINE MALEATE + PSEUDOEPHEDRINE HYDROCHLORIDE

110
210
potassium carbonate + sodium hydroxide
potassium carbonate + sodium hydroxide

1
4
ASCORBIC ACID + PARACETAMOL + POTASSIUM HYDROGEN CARBONATE
ASCORBIC ACID + PARACETAMOL + POTASSIUM HYDROGEN CARBONATE

3
6
CAFFEINE + PARACETAMOL PH. EUR.
CAFFEINE + PARACETAMOL PH. EUR.


select 
  id1, id2
, name1, name2
from (
  select 
    id_t1 id1
  , id_t2 id2
  , T1.name1 name1
  , T2.name2 name2
  from T1
    join T2 
      on  translate( replace( T1.name1, ' ', '' ), replace( T2.name2, ' ', '' ), '!' )
        = translate( replace( T2.name2, ' ', '' ), replace( T1.name1, ' ', '' ), '!' )
) ;


ID1
ID2
NAME1
NAME2


2
5
SODIUM HYDROGEN CARBONATE, SODIUM CARBONATE ANHYDROUS, CITRIC ACID
SODIUM HYDROGEN CARBONATE, SODIUM CARBONATE ANHYDROUS

3
6
CAFFEINE, PARACETAMOL PH. EUR.
PARACETAMOL PH. EUR.,CAFFEINE

100
10
PARACETAMOL, DEXTROMETHORPHAN, PSEUDOEPHEDRINE, PYRILAMINE
DEXCHLORPHENIRAMINE MALEATE, PSEUDOEPHEDRINE HYDROCHLORIDE

110
210
sodium hydroxide, potassium carbonate
sodium hydroxide, potassium carbonate

TOKEN	ID
ASCORBIC ACID	T1_1
SODIUM HYDROGEN CARBONATE	T1_2
CAFFEINE	T1_3
PSEUDOEPHEDRINE HYDROCHLORIDE	T1_4
PARACETAMOL	T1_100
sodium hydroxide	T1_110
POTASSIUM HYDROGEN CARBONATE	T2_4
SODIUM HYDROGEN CARBONATE	T2_5
PARACETAMOL PH. EUR.	T2_6
CODEINE PHOSPHATE	T2_7
DEXCHLORPHENIRAMINE MALEATE	T2_8
DEXCHLORPHENIRAMINE MALEATE	T2_10
PARACETAMOL	T2_200
sodium hydroxide	T2_210
potassium hydroxide	T2_211
PARACETAMOL	T1_1
SODIUM CARBONATE ANHYDROUS	T1_2
PARACETAMOL PH. EUR.	T1_3
DEXCHLORPHENIRAMINE MALEATE	T1_4
DEXTROMETHORPHAN	T1_100
potassium carbonate	T1_110
ASCORBIC ACID	T2_4
SODIUM CARBONATE ANHYDROUS	T2_5
CAFFEINE	T2_6
PARACETAMOL DC	T2_7
DEXTROMETHORPHAN HYDROBROMIDE	T2_8
PSEUDOEPHEDRINE HYDROCHLORIDE	T2_10
DEXTROMETHORPHAN	T2_200
potassium carbonate	T2_210
sodium carbonate	T2_211
POTASSIUM HYDROGEN CARBONATE	T1_1
CITRIC ACID	T1_2
PSEUDOEPHEDRINE	T1_100
PARACETAMOL	T2_4
PSEUDOEPHEDRINE	T2_200
PYRILAMINE	T1_100
PYRILAMINE1	T2_200

ID1	ID2	TOKENGROUP_T1	TOKENGROUP_T2
4	10	DEXCHLORPHENIRAMINE MALEATE + PSEUDOEPHEDRINE HYDROCHLORIDE	DEXCHLORPHENIRAMINE MALEATE + PSEUDOEPHEDRINE HYDROCHLORIDE
110	210	potassium carbonate + sodium hydroxide	potassium carbonate + sodium hydroxide
1	4	ASCORBIC ACID + PARACETAMOL + POTASSIUM HYDROGEN CARBONATE	ASCORBIC ACID + PARACETAMOL + POTASSIUM HYDROGEN CARBONATE
3	6	CAFFEINE + PARACETAMOL PH. EUR.	CAFFEINE + PARACETAMOL PH. EUR.

ID1	ID2	NAME1	NAME2
2	5	SODIUM HYDROGEN CARBONATE, SODIUM CARBONATE ANHYDROUS, CITRIC ACID	SODIUM HYDROGEN CARBONATE, SODIUM CARBONATE ANHYDROUS
3	6	CAFFEINE, PARACETAMOL PH. EUR.	PARACETAMOL PH. EUR.,CAFFEINE
100	10	PARACETAMOL, DEXTROMETHORPHAN, PSEUDOEPHEDRINE, PYRILAMINE	DEXCHLORPHENIRAMINE MALEATE, PSEUDOEPHEDRINE HYDROCHLORIDE
110	210	sodium hydroxide, potassium carbonate	sodium hydroxide, potassium carbonate