SQL Server and CTE

I recently posted a function to perform string replacement for T-SQL which we were using with a cursor to run through a some look up table and replace any occurences of the lookup text in the destination with the corresponding replacement text. After initial testing we were finding that the cursor was really killing performance so we needed a way to replace the cursor.  All roads led to CTEs (Common Table Expression)  but apart from a small function to create a list of people from a tree structure (recursive CTE), that a colleague had written, I had not seen many CTEs in action.

I had a few examples of recursive CTEs from around the web to build from and so I could easily get a CTE to recusively look through a table of lookups and replace a destination string with the corresponding string. The added complication was that there was actually several lookup tables combined into one lookup table and  first I had to link the look up table to an id in the destination table.  An example of the two tables are shown below:

ItemID	Lookup	Replacement
1	MX	MN
1	MY	CY
1	SB	DG
2	HG	TY
2	HD	OK

Table 1: The lookup table

ItemID	String	Result
1	MX+MY+SB
2	HD/HG

  Table 2: The string table with column for resultant string with replacements
The desired result is to have MN+CY+DG in row 1 column 3 and OK/TY in row 2 column 3.  The first idea was to just let the lookup navigate all the way through table 2 but this may have replaced strings in the result that were not linked to the item (i.e. If row2 of table 2 contained SB) and would be really inefficent as in the real system these tables could potentially contain 1000s of rows.