CODEWITHSUNDEEP
sqlite
mrbTT
mrbTT

Reputation: 1409

How do I replace all whitespace values to Null in all columns at SQLITE3?

I'm sorry I don't have code because I really don't know / didn't find how this is possible.

I've created a database from big TXT files that were separated by character length, making so that if the limit for - let's say - address field was 200 chars, but for 1 value it only had 50, the other 150 will be whitespace...example:

"                                                                        221 Baker Street, NY, NY"

Now, I have this 117gb database that I'm trying to lower the size (VACUUM did not work). I'm wondering if there is a way to update all columns by trim() all values

Upvotes: 0

Views: 254

Answers (1)

Thomas G
Thomas G

Reputation: 10206

Use either :

  • Ltrim to remove leading spaces
  • Rtrim to remove trailing spaces
  • Trim to remove leading and trailing spaces

Example:

UPDATE Thetable
SET column1=ltrim(column1, ' '), 
    column2=ltrim(column2, ' '), 
    column3=ltrim(column3, ' ')

Upvotes: 1

Related Questions