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?
>Solution :
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);