Resolve split string in subquery

I have two tables in a Microsoft SQL Database, one contains 3 columns with separated values (by comma and/or slash, but both should be equally treated as a separator). Then I have another Table that contains an ID which is the same as each item in the split string from TABLE1. I want to resolve the items in TABLE 1 so the Text from the matching row in TABLE2 is displayed. Is there a way to achieve this?

TABLE1

Text1           Text2           Text3
TA12,TA250      T1  
TA12,TA250      T1  
TA12,TA250      TA250,TA12      T310/T52
TA12,TA250      TA250           T310/T52

TABLE2

TA12            Hello
TA250           World
T1              This is a Test
T310            You are
T52             a Hero

Desired Result

Text1           Text2           Text3
Hello World     This is a Test  NULL
Hello World     This is a Test  NULL
Hello World     World Hello     You are a Hero
Hello World     World           You are a Hero

I’m able to achieve this using C#, but i would very much prefer this to happen on the SQL side.

Answer

In the most recent version of SQL Server, you can do:

select t.*, t1.new_text1, t2.new_text2, t3.new_text3
from table1 t outer apply
     (select string_agg(t2.col2) as new_text1
      from table2 t2
      where t2.col1 in (select * from string_split(replace(t.text1, '/', ','), ','))
     ) t1 outer apply
     (select string_agg(t2.col2) as new_text2
      from table2 t2
      where t2.col1 in (select * from string_split(replace(t.text2, '/', ','), ','))
     ) t2 outer apply
     (select string_agg(t2.col2) as new_text3
      from table2 t2
      where t2.col1 in (select * from string_split(replace(1.text3, '/', ','), ','))
     ) t3;

That said, fixing your data model should take precedence over trying to use it.