how to use replace on a left join statement

Question

I have two tables, one of them has a field with strings like that '1111AAA', the other one has the same field but with this structure '1111 AAA'. I want to replace the blank space with '', but I have an error near the ON of the left join.

I put the code below:

select idticket, bt.matricula, bv.vehicle
from b_ticket bt
left JOIN b_vehicle bv ON REPLACE(bv.matricula, ' ', '') ilike ON REPLACE(bt.matricula, ' ', '')
where date_start >= '2019/01/01/'
and date_end <= '2020/01/01'

I cannot change the values of any of the two tables. And I also try TRIM function, but it doesn't work because it removes blank spaces of the start and end of the string, not between words.

Any idea?

Thanks!

Error message: ERROR: sintax error near «ON» LINE 3: ... bv ON REPLACE(bv.matricula, ' ', '') ilike ON REPLACE...

Answer 1

Most likely you have got an extraneous ON keyword in your JOIN clause:

SELECT
    idticket, bt.matricula, bv.vehicle
FROM
    b_ticket AS bt LEFT JOIN b_vehicle AS bv
        ON REPLACE(bv.matricula, ' ', '') ILIKE REPLACE(bt.matricula, ' ', '')
WHERE
    date_start >= '2019/01/01/' AND date_end <= '2020/01/01';

As the error states: predicate must be any valid boolean expression, but it cannot have a ON keyword in it.