Multiple location to reference location dimension

I am trying to map values associated to my location dimension – dimension seen below:

LocationID	Country	State	City	Zip Code	Longitude	Latitude
1	USA	NY	Manhattan
2	USA	NY	Yonkers
3	USA	NY	Buffalo

I am receiving transaction data where multiple regions may be identified as a value. For example, I will get a value for city as such: Yonkers/Manhattan and Manhattan/Yonkers.

Transaction Data example:

TransID	From City	To City
1	Yonkers/Manhattan	Manhattan/Yonkers
2	Manhattan/Yonkers	Yonkers/Buffalo

The rule is that the first city in the ‘From City’ should be used when linking to the location dimension and the last city in the ‘To City’ should be used when linking to the location dimension. The final result should be as followed:

TransID	From City ID	To City ID
1	2	2
2	1	3

I would really appreciate any assistance on this.

Answer

I tried and got the required results by using inner join on the dimension table and transaction table. Use substring to join first city and last city in ON condition.

Query:

select distinct loc.TransID, dim_FC.LocationID as from_city_id, dim_LC.LocationID as to_city_id from trans_location loc
INNER JOIN dim_location dim_FC ON dim_FC.City = substring(loc.[From City], 1, charindex('/',loc.[From City])-1)
INNER JOIN dim_location dim_LC ON dim_LC.City = reverse(substring(reverse(loc.[To City]),1,charindex('/',REVERSE(loc.[To City]))-1))
order by loc.TransID