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...

Can you post the error (copy-paste) you have when issuing the query — jlandercy, Mar 05 '20 at 13:18

jlandercy · Accepted Answer · 2020-03-05T13:23:33.007

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.

edited Mar 05 '20 at 13:23

answered Mar 05 '20 at 13:21

jlandercy

7,183
1
39
57

oh... yes, I duplicate ON, thank you, I was looking around 30 minutes and I don't see that error... thanks! – Imrik Mar 05 '20 at 13:22
@Imrik, Yes it happens. Carefully reading the error must be the first step. Using indentation and typesetting rules also helps a lot because it increases readability and ease maintenance. Have a good day! – jlandercy Mar 05 '20 at 13:31

score 1 · Answer 2 · answered Mar 05 '20 at 13:21

1

This is not valid SQL:

left JOIN b_vehicle bv 
    ON REPLACE(bv.matricula, ' ', '') ilike ON REPLACE(bt.matricula, ' ', '')

There should be only one ON per JOIN. Presumably, you want:

left JOIN b_vehicle bv 
    ON REPLACE(bv.matricula, ' ', '') ilike REPLACE(bt.matricula, ' ', '')

answered Mar 05 '20 at 13:21

GMB

216,147
25
84
135

1

oh... yes, I duplicate ON, thank you, I was looking around 30 minutes and I don't see that error... thanks! – Imrik Mar 05 '20 at 13:22

how to use replace on a left join statement

2 Answers2