replace column values while searching from another table-CodePudding

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.

CodePudding user response：

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)

CodePudding user response：

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;