CODEWITHSUNDEEP
sqlsqlite
elig
elig

Reputation: 3058

How to remove surplus spaces at the end of a cell value in sqlite3?

I'm looking for a way to get rid of surplus spaces at the end of perfectly fine cell values. That would be all two spaces or more at the end of a cell value. Some cell values contain spaces between words. The sed command for such value would have been sed -re 's/\s{2,}*$//'. As far as I know REPLACE() doesn't support patterns at all nor backward replacement groups (\0 \1 and so on) and REGEXP() (from the externally-loaded library) doesn't do replacements.

Upvotes: 0

Views: 148

Answers (1)

GMB
GMB

Reputation: 222432

In SQLite, you can use string function rtrim() to remove spaces at the end of a string. 

select rtrim(mycol) from mytable

If you want to remove trim only values that have at least two spaces and leave values that end with a single space untouched, you can wrap this in a condition:

select case when mycol like '%  ' then rtirm(mycol) else mycol end from mytable

Or if you are looking for an update statement:

update mytable set mycol = rtrim(mycol) where mycol like '%  '

Upvotes: 1

Related Questions