SQLite - Left Join with Substr

Question

select distinct substr(value,2,4),summary 
from ticket_custom 
LEFT JOIN ticket On ticket_custom.substr(value,2,4) = ticket.id 
where name = "parent" AND value <> 0

I have a table ticket_custom where the ticket-number has a hastag in front of the ticketnumber (e.g. "#4567"), but in the table ticket, the ticketnumber is saved without this hashtag ("4567"). I would like to left join these two tables and would like to use the "summary"-value of the table ticket right next to the ticket-number without the #. I tried the above query, but I am getting an error. How can I combine substr = using the ticketnumber without the # with a left join? Or is there another way to do this?

Thanks!

Answer 1

You should be doing substr(ticket_custom.value,2) instead of ticket_custom.substr(value,2,4) (syntax was incorrect, and string length isn't guaranteed only to be 5 chars)

So your query should look like:

select distinct substr(value,2),summary 
from ticket_custom 
LEFT JOIN ticket On substr(ticket_custom.value,2) = ticket.id 
where name = "parent" AND value <> 0