CODEWITHSUNDEEP
t-sqlsql-server-ce
Louis Waweru
Louis Waweru

Reputation: 3672

Remove last character from string column in SQL Server CE

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.

Upvotes: 7

Views: 48816

Answers (3)

Artur Kryk
Artur Kryk

Reputation: 11

The proposed solution with using SET col = SUBSTRING(col, 0, LEN(col)) is a bit unclear.

This is working as a side effect of the SUBSTRING second parameter starting_position being "1 based". So 0 in this case is kind of negative (you could also use i.e. -3 and 4 characters would be stripped then instead of 1). IMHO it would be much more clear to use this:

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

Which shows the code's intent

Upvotes: 1

Matthew Caughey
Matthew Caughey

Reputation: 3

    UPDATE myTable
    SET col = SUBSTR(col, 0, (LENGTH(col) - 1))
    WHERE col LIKE '%,';

Upvotes: 0

Louis Waweru
Louis Waweru

Reputation: 3672

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

Upvotes: 17

Related Questions