Error in rsqlite_send_query(conn@ptr, statement) : near "(": syntax error

Question

The following line of code:

sqldf("UPDATE q1_sql_1 SET MONTH_YEAR = RIGHT(MONTH_YEAR, LEN(MONTH_YEAR) - 4)")

Shows this error:

Error in rsqlite_send_query(conn@ptr, statement) : near "(": syntax error

However I don't get why the error message is `syntax error`, it should be `no such function: RIGHT`. That's strange. — Scarabee, Feb 12 '18 at 13:51
Only half of it is a duplicate. There are actually two errors in the sql statement. See my answer. — G. Grothendieck, Feb 12 '18 at 13:54
@G.Grothendieck I agree. Do you have any idea why `select right('abc', 2)` reports a syntax error instead of an unknown function? — Scarabee, Feb 12 '18 at 13:57
Yes. I guess it's because of the ambiguity with the `right` of `right join`. — Scarabee, Feb 12 '18 at 14:20
@G.Grothendieck: In case you're interested, I've asked and it turns out it's indeed because of the ambiguity with the `RIGHT` keyword. http://sqlite.1065341.n5.nabble.com/Improper-error-message-td100720.html — Scarabee, Feb 21 '18 at 01:08

score 1 · Accepted Answer · answered Feb 12 '18 at 13:51

Use length and rightstr. For the functions available in SQLite see:

and for the contributed functions that RSQLite also makes available:

1 Answers1