LEFT OUTER JOIN with LIKE in SQLite not working

Question

I think I'm going mad, but in SQL server management studio this query works perfectly well:

INSERT INTO [#Temp_main]
SELECT T1.ID, T2.ID
FROM [#Temp_other] T1
LEFT OUTER JOIN [Table_with_lots_of_data] T2 ON ((T2.ID LIKE T1.ID+'%')

In other words, table Temp_other contains incomplete IDs (only the first few characters), and I want to match these to the ID in Table_with_lots_of_data, and insert the results into Temp_main.

The application I'm writing, which uses SQLite databases, was only returning the T1.ID (i.e. no complete T2.ID), so I popped it into the DB browser for SQLite for testing, and lo and behold this also returned only the T1.ID.

Any help as to what I'm missing with SQLite? I'm guessing it's something to do with the T1.ID + '%' wildcard bit?

Answer 1

|| is a concatenation operator is SQLite, not +:

(T2.ID LIKE T1.ID || '%')