How to JOIN two SQL tables if one of the columns is text and contains extra data?

Question

SQL

There are two tables that need to be JOIN . The problem is that in the first table the required column "id" is in int64, and in the second table it is in the form of text with a prefix ("abc123", "abc456"). Converting the "id" of the first table into text and adding a prefix to it, or trimming the column prefix of the second table and converting it to a number - does not work.

Mistake -

JOIN: each equality predicate argument must depend on exactly one JOIN input

Answer 1

SELECT a.id, b.comment
FROM a
INNER JOIN b
ON a.id = CAST(SUBSTRING(b.prefid, 4) AS UNSIGNED)