SQL - selection of values ​​of several keywords from one field

Question

So, I am creating an SQL view for more readable data, which I will use for reporting. I have one table that stores field data (keyword identifiers) for questions in a section on a website. Column 3 is a field with several keywords and is stored in DB limited by chr (185).

Table 1

    Column1 | Column2 | Column3
       4456 |    2323 | ¹8661¹8662¹

I have a second table in which the identifiers of keywords and their meanings are placed.

table 2

  Column1  | Column2
     4456  |    val1
     2323  |    val2
     8661  |    val3
     8662  |    val4

A view joins tables to display keyword values, but I'm not sure how to handle a field with multiple keywords (looking at the format of the result, as shown below).

View table

    Column1 | Column2 | Column3
       val1 |    val2 | val3; val4

Do I need some function to accomplish this or is there another way?

Answer 1

SQL Server XML.. , fiddle:

with mapped (c1, c2, val) as ( 
    select t1.column1, t1.column2, t2.column2
    from table1 t1
    cross apply (select convert(xml, '<_' + replace(substring(column3,2,len(column3)-2), '¹', '/><_') + '/>')) s(c)
    cross apply c.nodes('*') x(n)
    join table2 t2 on t2.column1=n.value('fn:substring(local-name(.),2)', 'int'))

select
    column1 = c1.column2,
    column2 = c2.column2,
    column3 = (select val + '; ' from mapped where c1=t1.column1 and c2=t1.column2 for xml path(''))
from table1 t1
join table2 c1 on c1.column1=t1.column1
join table2 c2 on c2.column1=t1.column2