How to replace the Alphabet from word in SQL server 2008

Question

My question is to bring the below result using table 1 and 2 respectively.

TABLE 1

MQGRP   MQCOMPET
1   AV
2   UW|AV

Table 2

FullForm RecordKey
AVALON   AV
UWARON   UW

Result

MQGRP   MQCOMPET
1   AVALON
2   UWARON|AVALON

Answer 1

The problem is that your DB is not normalized, so you cannot make a simple query to get what you need.

First you need to split the first table in rows like this:

1 AV
2 AV
2 UW

You need to create a table valued function that splits the MQCOMPET of the first table, and use it with CROSS APPLY or OUTER APPLY to get this first solution to your problem. See this article to understand how they work. See also this SO Q&A.

Then you need to join the rsult with table 2, to get something like this

1 AV AVALON
2 AV AVALON
2 UW UWARON

And finally you can use this technique to concatenate the values again: Concatenate many rows into a single text string?

Answer 2

Create a little Split Functions 1st

Split Functions Definition

CREATE FUNCTION dbo.Split_Using_XML
 (
   @InputString NVARCHAR(MAX),
   @delimiter varchar(1) 
 )
 RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
    DECLARE @xml AS XML;

    SET @xml  = CAST(('<X>'+REPLACE(@InputString,@delimiter ,'</X><X>')+'</X>') AS XML)

    INSERT INTO @t(val)
    SELECT C.value('.', 'varchar(10)') AS value
    FROM @xml.nodes('X') as X(C)
 RETURN;
END

Test Data

DECLARE @TABLE1 TABLE (MQGRP INT,  MQCOMPET VARCHAR(100))
INSERT INTO @TABLE1 VALUES 
(1   ,'AV'),
(2   ,'UW|AV')

DECLARE @Table2 TABLE (FullForm VARCHAR(100), RecordKey VARCHAR(100))
INSERT INTO @Table2 VALUES
('AVALON', 'AV'),
('UWARON', 'UW')

Query

;WITH CTE
AS (
    SELECT t.MQGRP
           ,t2.FullForm AS MQCOMPET
    FROM @TABLE1 t
            CROSS APPLY (SELECT val FROM dbo.Split_Using_XML(t.MQCOMPET, '|'))C(VAL)
    INNER JOIN @Table2 t2 
    ON C.val = t2.RecordKey
   ) 
SELECT C.MQGRP
      ,STUFF(( SELECT ' | ' + MQCOMPET
              FROM CTE
              WHERE MQGRP = C.MQGRP
              FOR XML PATH(''),TYPE).
              value('.','NVARCHAR(MAX)'),1,2,'') AS MQCOMPET
FROM CTE C
GROUP BY C.MQGRP

Result Set

╔═══════╦══════════════════╗
║ MQGRP ║     MQCOMPET     ║
╠═══════╬══════════════════╣
║     1 ║  AVALON          ║
║     2 ║  UWARON | AVALON ║
╚═══════╩══════════════════╝