SQL how to link 2 tables varchar + number?

Question

Oracle

Hey!!
In the table there is a varchar list separated by commas like
1,11,32,531
2, 44, 321
each list has its own ID ,
and in another table the number values ​​​​with numbers from the lists that are above with the description
1 Vasya
11 Petya
32 Kostya
531 Dima
and so on
tell me how to link two tables?
in the end it should turn out:
1 Vasya ID (from the table above)

Answer 1

As already noted - the structure is not very ...
But sometimes this is necessary. And if this is not a one-time thing, then it will be very convenient to make a function that receives a string [+ parameter - separator character] as input and returns a table with parsed "numbers" from the string. Then it will be possible to use the result of this function as one of the parts of join for example.

Answer 2

with great_design_tab as (
  select 700 as id, '1,11,32,531' as ass_pain from dual union all
  select 701 as id, '2, 44, 321' as ass_pain from dual
)
,usrs as (
  select 1 as id, 'вася' as username from dual union all
  select 11 as id, 'петя' as username from dual union all
  select 32 as id, 'костя' as username from dual union all
  select 531 as id, 'дима' as username from dual union all
  select 2 as id, 'жора' as username from dual union all
  select 44 as id, 'изя' as username from dual union all
  select 321 as id, 'семен' as username from dual
)
select u.id, u.username, t.id
  from usrs u
      ,great_design_tab t
      ,lateral (
                select level x from dual 
                connect by regexp_substr(ass_pain, '[^,]+', 1, level) is not null
               )
 where u.id = trim(regexp_substr(ass_pain,'[^,]+', 1, x));

ID USERNAME         ID
---------- -------- ----------
         1 вася            700
        11 петя            700
        32 костя           700
       531 дима            700
         2 жора            701
        44 изя             701
       321 семен           701

Answer 3

STRING_SPLIT

Answer 4

do you mean connect ?
You need something like
select t1.id from t1, t2 where t1.s like "%" || CONVERT(t2.id) || "%" ?
But in general, you have a fundamentally wrong table structure.