join tables based on partial string sql

I have two tables:

regional

imd

The regional contains a location column with information about cities of UK. The imd table contains a column ‘Name’ which also includes the city names.

I want to join both tables based on the location and Name but in regional table, the location also includes the country name whereas the imd table only contains the city name. I want to join tables based on partial string match I’ve tried the following code but it is not working.

SELECT *
FROM s.regional, s.imd
WHERE s.regional.location like concat('%',s.imd.Name)

Answer

You could take the first part of the [location] field in the [regional] table before the comma and join it to the [name] column in the [imb] table like this:

SELECT a.*,b.*
FROM s.regional a LEFT JOIN s.imb b ON SPLIT_PART(a.location, ',', 1)  = b.[name]

Ideally you should move the [location] field into it’s own table where you can have columns like city, Country etc and create a reference to that table (foreign key). It will make it easy to maintain(Insert/Update/Delete) you tables in the future.