时间戳

首页 > 解决方案 > how to use replace on a left join statement

string - how to use replace on a left join statement

问题描述

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

标签： stringpostgresqlreplaceleft-jointrim

解决方案

很可能您的子句中有一个无关的ON关键字：JOIN

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';

正如错误所述：谓词必须是任何有效的布尔表达式，但其中不能有ON关键字。

推荐阅读