By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.30 |
CREATE TABLE table1 (columnB INTEGER);
INSERT INTO table1 VALUES
(1256),
(12345),
(1256),
(5678),
(1256),
(78905)
Records: 6 Duplicates: 0 Warnings: 0
CREATE TABLE table2 (columnA INTEGER);
INSERT INTO table2 VALUES
(12345),
(56789),
(34890),
(78905)
Records: 4 Duplicates: 0 Warnings: 0
SELECT table1.columnB
FROM table1
INNER JOIN table2 ON table2.columnA = table1.columnB
| columnB |
|---|
| 12345 |
| 78905 |
-- back to the data provided in the question
delete from table1;
insert into table1 VALUES
(12345),
(567890),
(1256),
(78905);
delete from table2;
insert into table2 VALUES
(123456),
(56789),
(34890),
(789056);
Records: 4 Duplicates: 0 Warnings: 0
Records: 4 Duplicates: 0 Warnings: 0
SELECT
columnB as result
FROM table1
CROSS JOIN table2 ON LEFT(table2.columnA,LEAST(length(columnB),length(columnA))) =
LEFT(table1.columnB,LEAST(length(columnB),length(columnA)))
| result |
|---|
| 12345 |
| 567890 |
| 78905 |