replace column values while searching from another table

Question

I have a below column values in a table

Destination
DELLKO
DELBOM
DELVGA 
BOMIXY

and so on

and another table with the following data

Table name -- CITY

CityName    Code
Agartala    IXA
Agatti      AGX
Agra        AGR 
Akola       AKD
Allahabad   IXD
Aurangabad  IXU
Siliguri    IXB
Bareilly    BEK
Lucknow     LKO
DELHI       DEL
BOM         MUMBAI

and so on

now I would like the output something like that

DELHI|Lucknow
DELHI|MUMBAI

whith the help of Table CITY i.e. wherever destination column has DEL it should be replace by DELHI append pipe sign and search LKO and append LUCKNOW.

Just join your table using left(Destination,3) and right(Destination,3) with CITY table (2 different joins)? — James Z
– James Z, Commented Dec 19, 2021 at 17:22
Are you sure about the 'BOM'/'MUMBAI' row in table CITY? Might it have the column values swapped? — HABO
– HABO, Commented Dec 20, 2021 at 13:41

RomanG · Accepted Answer · 2021-12-19 18:11:11Z

Let me know if code of city is more than 3 letters or less then 3 letters. For now, Lets assume that code is 3 letters only. Try this:


-- Temp tables for example:
--========================================================

if OBJECT_ID('tempdb..#Abbreviation') is null
    begin
        create table #Abbreviation(
            destination char(6)
        );
    end
else
    begin
        truncate table #Abbreviation;
    end;

if OBJECT_ID('tempdb..#City') is null
    begin
        create table #City(
            cityName varchar(50)
            , code char(3)
        );
    end
else
    begin
        truncate table #City;
    end;


-- Query:
--========================================================

with cte_main as(
    select
        left(destination, 3) partOneCode
        , right(destination, 3) partTwoCode

    from #Abbreviation
)
, cte_preSet as(
    select
        main.partTwoCode
        , sub.cityName cityNameOne

    from cte_main main
        inner join
        #City sub
        on main.partOneCode = sub.code
)
select
    main.cityNameOne + '|' + sub.cityName

from cte_preSet main
    inner join
    #City sub
    on main.partTwoCode = sub.code;

LukStorms · Accepted Answer · 2021-12-19 18:06:24Z

0

Join CITY twice on the destination codes.

SELECT CONCAT(city1.CityName,'|',city2.CityName) AS Destination
FROM Destinations dest
LEFT JOIN CITY city1 ON city1.Code = LEFT(dest.Destination, 3)
LEFT JOIN CITY city2 ON city2.Code = RIGHT(dest.Destination, 3)

edited Dec 19, 2021 at 18:06

answered Dec 19, 2021 at 18:01

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Collectives™ on Stack Overflow

replace column values while searching from another table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related