CODEWITHSUNDEEP
sqlsqlite
Winston C. Yang
Winston C. Yang

Reputation: 1507

How to format many values in a database?

I have a database (in SQLite) in which some entries (or possibly all) are strings whose first character is a space.

The database may be small enough for me to export it as a CSV file and do a regular-expression search-and-replace which will delete the leading space. Is there an SQL statement which can achieve the same result?

(The database has over 60 columns---listing each one might get tedious.)

Upvotes: 1

Views: 53

Answers (2)

zerkms
zerkms

Reputation: 254944

You can strip the unneeded spaces right in select query:

SELECT TRIM(field)

or do it once on all rows

UPDATE table SET field = TRIM(field)

Upvotes: 2

miku
miku

Reputation: 188064

Take a look at thr trim family of functions, e.g. ltrim.

ltrim(X), ltrim(X,Y)

The ltrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the left side of X. If the Y argument is omitted, ltrim(X) removes spaces from the left side of X.

More: http://www.sqlite.org/lang_corefunc.html

Upvotes: 1

Related Questions