Remove last character from string column in SQL Server CE

Question

In SQL Server Compact, I'm trying to remove a trailing comma that came from a goof which affected several thousand rows of a NVARCHAR column.

UPDATE myTable 
SET col = LEFT(col, LEN(col)-1) 
WHERE col LIKE '%,';

throws the error:

There was an error parsing the query. [ Token in error = LEFT ]

Can SQL Server CE not parse that query? Or, can someone offer another approach?

Note: I tried this in CompactView, I'm not sure if that's the problem.

Answer 1

Based off this example I was able to get it done using SUBSTRING:

UPDATE myTable
SET col = SUBSTRING(col, 0, LEN(col))
WHERE col LIKE '%,';