Joining tables on substrings-CodePudding

Let's say I have two database tables called cars and boats. In both tables I have a column called country.

In the cars table, country is stored like this: SE:SV:Sweden:Sverige.

In the boats table, country is stored like this: Sweden:SWE:01.

I need to somehow join these tables on the substring Sweden. How would I go about doing that? A trigger? Or can I add new column to each table and do somekind of operation in it?

CodePudding user response：

Your table design is seriously suboptimal, because you are storing multiple points of data as colon-separated data points in a single column. That being said, if you must proceed with this design, you can use SUBSTRING_INDEX to join:

SELECT *
FROM cars c
INNER JOIN boats b
    ON SUBSTRING_INDEX(b.country, ':', 1) =
       SUBSTRING_INDEX(SUBSTRING_INDEX(c.country, ':', -2), ':', 1);