CODEWITHSUNDEEP
sqlsql-server
Gitz
Gitz

Reputation: 820

Replace comma separated values

I have two tables where 1st table is Englishgermankwds_tbl and 2nd is Kwd_UploadRecored the data of the tables is as follows

Table Englishgermankwds_tbl

English_Keywords                 German_Keywords
architecture                     Architektur
Arrival                          Ankunft
aspirations                      Lebensziel
attire                           Kleidung
Blueprint                        Technische Zeichnung
Carrying                         Tragen
caucasian appearance             Europäischer Abstammung
cheerful                         fröhlich
clothes                          Kleidung
color image                      Farbbild
day                              Tag
Development                      Entwicklung
differential focus               Geringe Tiefenschärfe
focus on foreground              Fokus auf den Vordergrund
front view                       Vorderansicht
Full Length                      Ganzkörperansicht
growth                           Wachstum
Happiness                        Glücklichsein,Glück
Hardhat                          Bauarbeiterhelm

Table Kwd_UploadRecored

ID  Primary_Kwd                             Sec_Kwd                                         Main_Kwd
1   Man,One Man,architecture,Boy            Arrival,Sigle Man , Business Man ,Male          aspirations,One Person
2   Woman,attire,Girl                       Girl,Girls,Female,Blueprint,Carrying,           Teenage Girl,Only Girls
3   Grand father,Man,caucasian appearance   cheerful, Family,Fatherhood,Family Member,      Male Parent,
4   Baby ,clothes,color image               growth,Babies,Child,Happiness                   Children,Toddlers,differential focus,

I want to Match the All English keywords of table Kwd_UploadRecored from the table Englishgermankwds_tbl and replace the finding keywords to German Keywords.

Expected result like- 

ID  Primary_Kwd                                 Sec_Kwd                                         Main_Kwd
1   Man,One Man,Architektur,Boy                 Ankunft,Sigle Man , Business Man ,Male          Lebensziel,One Person
2   Woman,Kleidung,Girl                         Girl,Girls,Female,Technische Zeichnung,         Teenage Girl,Only Girls
3   Grand father,Man,Europäischer Abstammung    fröhlich, Family,Fatherhood,Family Member,      Male Parent,
4   Baby ,clothes,Farbbild                      Wachstum,Babies,Child,Glücklichsein             Children,Toddlers,differential focus,

Please help how to achieve this.

Upvotes: 2

Views: 2111

Answers (2)

Sarath Subramanian
Sarath Subramanian

Reputation: 21301

I have written logic inside the query

;WITH CTE AS
(
    SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'KeyWords' 
    FROM  
    (
         -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
         SELECT CAST ('<M>' + REPLACE(Primary_Kwd, ',', '</M><M>') + '</M>' AS XML) AS Data 
         FROM Kwd_UploadRecored     
    ) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a)

    UNION ALL

    SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'KeyWords' 
    FROM  
    (
         -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
         SELECT CAST ('<M>' + REPLACE(Sec_Kwd, ',', '</M><M>') + '</M>' AS XML) AS Data 
         FROM Kwd_UploadRecored     
    ) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a)

    UNION ALL

    SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'KeyWords' 
    FROM  
    (
         -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
         SELECT CAST ('<M>' + REPLACE(Main_Kwd, ',', '</M><M>') + '</M>' AS XML) AS Data 
         FROM Kwd_UploadRecored     
    ) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a)
)
SELECT T.English_Keywords, T.German_Keywords
FROM CTE C
JOIN Englishgermankwds_tbl T ON C.KeyWords=T.English_Keywords

UPDATE

Here is the query that does your expected output.

;WITH CTE AS
(
    -- Since CSV values is scattered with non-alphabetical order, we use ROW_NUMBER()
    -- to maintain the order by default
    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT(0))) RNO,'Primary_Kwd' Colum 
    FROM
    (
        -- Convert CSV to rows
        SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'KeyWords' 
        FROM  
        (
             -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
             SELECT ID,CAST ('<M>' + REPLACE(Primary_Kwd, ',', '</M><M>') + '</M>' AS XML) AS Data 
             FROM #Kwd_UploadRecored     
        ) AS A 
        CROSS APPLY Data.nodes ('/M') AS Split(a)
    )TAB

    UNION ALL

    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT(0))) RNO,'Sec_Kwd'  
    FROM
    (
        SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'KeyWords' 
        FROM  
        (
             -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
             SELECT ID,CAST ('<M>' + REPLACE(Sec_Kwd, ',', '</M><M>') + '</M>' AS XML) AS Data 
             FROM #Kwd_UploadRecored     
        ) AS A 
        CROSS APPLY Data.nodes ('/M') AS Split(a)
    )TAB

    UNION ALL

    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT(0))) RNO,'Main_Kwd'  
    FROM
    (
        SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'KeyWords' 
        FROM  
        (
             -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
             SELECT ID,CAST ('<M>' + REPLACE(Main_Kwd, ',', '</M><M>') + '</M>' AS XML) AS Data 
             FROM #Kwd_UploadRecored     
        ) AS A 
        CROSS APPLY Data.nodes ('/M') AS Split(a)
    )TAB
)
,CTE2 AS
(
    -- Check for German word, if matched German word else English
    SELECT C.ID,C.RNO,C.Colum,ISNULL(T.German_Keywords,C.KeyWords) German_Keywords 
    FROM CTE C
    LEFT JOIN #Englishgermankwds_tbl T ON C.KeyWords=T.English_Keywords 
) 
,CTE3 AS
(
    -- Convert back to CSV values with the old order of strings
    SELECT  ID,COLUM,
    SUBSTRING(
            (SELECT  ', ' + German_Keywords
            FROM CTE2 
            WHERE C2.Id=Id AND C2.COLUM=COLUM 
            ORDER BY RNO
            FOR XML PATH('')),2,200000) German_Keywords
    FROM CTE2 C2
)
-- Now we convert back Primary_Kwd,Sec_Kwd,Main_Kwd to columns with CSV values
SELECT ID,
MIN(CASE Colum WHEN 'Primary_Kwd' THEN German_Keywords END) Primary_Kwd,
MIN(CASE Colum WHEN 'Sec_Kwd' THEN German_Keywords END) Sec_Kwd,
MIN(CASE Colum WHEN 'Main_Kwd' THEN German_Keywords END) Main_Kwd 
FROM CTE3
GROUP BY ID

UPDATE 2

After closing the bracket of CTE3 give the below code

UPDATE Kwd_UploadRecored 
SET Primary_Kwd = TAB.Primary_Kwd,
Sec_Kwd = TAB.Sec_Kwd,
Main_Kwd = TAB.Main_Kwd
FROM
(
    SELECT ID,
    MIN(CASE Colum WHEN 'Primary_Kwd' THEN German_Keywords END) Primary_Kwd,
    MIN(CASE Colum WHEN 'Sec_Kwd' THEN German_Keywords END) Sec_Kwd,
    MIN(CASE Colum WHEN 'Main_Kwd' THEN German_Keywords END) Main_Kwd 
    FROM CTE3
    GROUP BY ID
)TAB
WHERE Kwd_UploadRecored.ID=TAB.ID

Upvotes: 4

koushik veldanda
koushik veldanda

Reputation: 1107

Working try this.... It will helps you 

DECLARE @MyTable TABLE (id INT IDENTITY(1,1), COLUMN1 VARCHAR(50),coulmn2 VARCHAR(50))

insert into @mytable  

select  a.column1,a.column2 from Englishgermankwds_tbl as ga outer apply
( select replace(gs.Primary_Kwd  ,ga.English_Keywords,ga.German_Keywords) as column1,replace(gs.j,ga.a,ga.j) as column2 from Kwd_UploadRecored as gs)a 

select top(select count(*)/4from @mytable)column1,coulmn2 from @mytable where id%2=0

Upvotes: 0

Related Questions