CODEWITHSUNDEEP
sqlsql-servert-sql
Simon GIS
Simon GIS

Reputation: 1055

Replace strings from a table to another Column

I have 2 tables in SQL-Server:

1st table is Product

ID       NameEnglish          NameFrench 
1        Box of Banana          NA
2        Product apple          NA
3        Pallet of Bread        NA

2nd table Translation

NameEnglish       NameFrench 
Banana            Banane
apple             Pomme
Bread             Pain

I want to update the 1st table with the following in the column (Namefrench) based on my table Translation

ID       NameEnglish             NameFrench 
1        Box of Banana          Box of Banane 
2        Product apple          Product Pomme
3        Pallet of Bread        Pallet of Pain

How I can Replace and Update my column NameFrench?

Upvotes: 1

Views: 59

Answers (2)

prem
prem

Reputation: 323

One more way:

UPDATE t1 SET namefrench = REPLACE(t1.nameenglish, RIGHT(RTRIM(t1.NameEnglish), LEN(t2.NameEnglish)), t2.NameFrench) 
        FROM table1 t1 
        INNER JOIN table2 t2 
        ON RIGHT(RTRIM(t1.NameEnglish), LEN(t2.NameEnglish)) = t2.NameEnglish

Upvotes: 0

Pranav Singh
Pranav Singh

Reputation: 20081

Try this :

UPDATE P 
    SET P.NameFrench= REPLACE(P.NameEnglish,T.NameEnglish,T.NameFrench)
FROM Product P 
    INNER JOIN Translation T ON P.NameEnglish LIKE '%' + T.NameEnglish + '%'

Here it is joined on Product NameEnglish which contains Translation NameEnglish. Also used replace Translation NameEnglish with NameFrench in Product NameEnglish column.

Reference:

REPLACE (Transact-SQL)

How to use column name as part of a LIKE statement in a WHERE clause of a JOIN

Upvotes: 4

Related Questions