Syntax Error: ON RIGHT when trying to match a substring in Impala

Question

Does anyone know why I am receiving this error? I am using SQL in IMPALA and it wont run. Theres a yellow underline under mem_register_hsty_view and transparency_services_summary_2018.

Here is my code:

use sndbx_dx;

SELECT
    r.member_identifier,
    n.fst_nme
FROM mem_register_hsty_view n
JOIN transparency_services_summary_2018 r
    ON RIGHT(TRIM(r.member_identifier),4) = LEFT(n.fst_nme,4)
ORDER BY
    r.id_key,
    r.group_number,
    n.fst_nme;

Here is the error:

AnalysisException: Syntax error in line 1:undefined: ...ervices_summary_2018 r ON RIGHT(TRIM(r.member_identifi... ^ Encountered: RIGHT Expected: CASE, CAST, DEFAULT, EXISTS, FALSE, IF, INTERVAL, NOT, NULL, REPLACE, TRUNCATE, TRUE, IDENTIFIER CAUSED BY: Exception: Syntax error

Answer 1

From the current Impala documentation the functions for taking some number of characters from the left or right of the string appear to actually be STRLEFT and STRRIGHT, respectively. Apply this to your current query gives:

SELECT
    r.member_identifier,
    n.fst_nme
FROM mem_register_hsty_view n
INNER JOIN transparency_services_summary_2018 r
    ON STRRIGHT(TRIM(r.member_identifier), 4) = STRLEFT(n.fst_nme, 4)
ORDER BY
    r.id_key,
    r.group_number,
    n.fst_nme;