How to update a column based on multiple string of another column in SQL Server?

I have a table with two strings in a column. I am trying to update the multicode column based on the EN in Lookup table. Below are the tables:

#Lookup table

#Table that must be updated

#Expected Table output

Current code:

select EN, ID From #TargetFlatten1
select ResponseCode, EN, Multicode From #targettest

One option turns the CSV string to rows with string_split(), does the lookup, then aggregates back the codes with string_agg:

select t.responseCode, t.en, x.multicode
from mytable t
cross apply (
    select string_agg(l.responseCode) within group (order by ordinal) as multicode
    from string_split(t.en, ',', 1)
    inner join mylookup l on l.en = value
) x

value and ordinal are two columns generated by string_split wich store (resp) the single value and its original position in the CSV list.

If you wanted an update, we can turn the logic to a correlated subquery:

update t
set t.multicode = (
    select string_agg(l.responseCode) within group (order by ordinal)
    from string_split(t.en, ',', 1)
    inner join mylookup l on l.en = value
)
from mytable t

Here is a way to do it using STRING_SPLIT with param 1 to enable ordinal (sql server 2022 ):

SELECT t2.ResponseCode, t2.EN, string_agg(ID, ',') WITHIN GROUP ( ORDER BY ordinal ) 
FROM table2 t2
CROSS APPLY STRING_SPLIT(t2.EN, ',', 1) AS s
LEFT JOIN table1 t1 on t1.EN = TRIM(s.value)
GROUP BY t2.ResponseCode, t2.EN

The update statement can be :

UPDATE t
SET t.Multicode = s.Multicode
FROM table2 t
INNER JOIN (
  SELECT t2.ResponseCode, t2.EN, string_agg(ID, ',') WITHIN GROUP ( ORDER BY ordinal ) AS Multicode 
  FROM table2 t2
  CROSS APPLY STRING_SPLIT(t2.EN, ',', 1) AS s
  LEFT JOIN table1 t1 on t1.EN = TRIM(s.value)
  GROUP BY t2.ResponseCode, t2.EN
) AS S on s.ResponseCode = t.ResponseCode and s.EN = t.EN

Demo here

For SQL SERVER 2017 & 2019 we can use OPENJSON to preserve ordinal :

SELECT t2.ResponseCode, t2.EN, string_agg(ID, ',') WITHIN GROUP ( ORDER BY [key] ) AS Multicode
FROM table2 t2
CROSS APPLY OPENJSON('["' +  REPLACE(t2.EN,',','","') + '"]') AS s
LEFT JOIN table1 t1 on t1.EN = TRIM(s.value)
GROUP BY t2.ResponseCode, t2.EN

Update statement :

UPDATE t
SET t.Multicode = s.Multicode
FROM table2 t
INNER JOIN (
  SELECT t2.ResponseCode, t2.EN, string_agg(ID, ',') WITHIN GROUP ( ORDER BY [key] ) AS Multicode 
  FROM table2 t2
  CROSS APPLY OPENJSON('["' +  REPLACE(t2.EN,',','","') + '"]') AS s
  LEFT JOIN table1 t1 on t1.EN = TRIM(s.value)
  GROUP BY t2.ResponseCode, t2.EN
) AS S on s.ResponseCode = t.ResponseCode and s.EN = t.EN

Demo here

Assuming you are stuck with such a terrible schema, and assuming your data does have spaces in odd places like your example, and assuming you don't have any lookup values with comma's in them ... this should work in ms sql server - don't ask me about other flavors

select 
  m.response_code
  ,m.en
  ,string_agg(l.id, ',') multicode
from 
  multicodes m
left join
  lookup l on l.en in (select trim(value) from string_split(m.en, ','))
group by
  m.response_code
  ,m.en