sql server - T-SQL How to translate multiple sub-strings to new values -

first of all, sorry because don't know how title problem.

my situation is, have 1 lookup table format:

+----+-----------+------------+ | id | fruit     |  color     | +----+-----------+------------+ |  1 | banana    | yellow     | |  2 | apple     | red        | |  3 | blueberry | notyetblue | +----+-----------+------------+ 

and main table this:

+-------+------------------------+------------+ | mixid |        contains        | mixedcolor | +-------+------------------------+------------+ |     1 | banana                 |            | |     2 | apple:blueberry        |            | |     3 | banana:apple:blueberry |            | +-------+------------------------+------------+ 

i want make look-up on first table , fill in mixedcolor column below:

+-------+------------------------+-----------------------+ | mixid |        contains        |      mixedcolor       | +-------+------------------------+-----------------------+ |     1 | banana                 | yellow                | |     2 | apple:blueberry        | red:notyetblue        | |     3 | banana:apple:blueberry | yellow:red:notyetblue | +-------+------------------------+-----------------------+ 

any appreciated.

thank you

i agree ideally table structure should altered. but, can want with:

select   mixid, [contains], stuff((           select ':' + color           table1           ':'+b.[contains]+':' '%:'+a.fruit+':%'                 xml path('')             ), 1, 1, '') color table2 b group mixid, [contains] 

demo: sql fiddle