How do I remove all spaces from a field in a Postgres database in an update query?

user_898454 0
Viewed 91783

What would be the proper syntax used to run an update query on a table to remove all spaces from the values in a column?

My table is called users and in the column fullname some values look like 'Adam Noel'. I want to remove the space so that the new value is 'AdamNoel'

I have like 30k rows

postgresql
7 Answers
update users
  set fullname = replace(fullname, ' ', '');
user_3303150

Just use the simple code

 REPLACE('some_string', ' ', '')

or

Replace('some_string', '\s', '')

to remove any white space from the string

user_90574030

You can include a condition to update only values that need it with the replace.

UPDATE users SET fullname = REPLACE(fullname, ' ', '') WHERE fullname ~* ' ';

Quick and dirty

user_21702860

Can perform an update all with the trim function.

UPDATE users AS u SET name = TRIM(u.name)

Optionally add a clause to update only the records that need it, instead of all, but uses more CPU.

UPDATE users AS u SET name = TRIM(u.name) WHERE LENGTH(TRIM(u.name)) <> LENGTH(u.name)

If the table has a unique index on the value being trimmed, you could get a duplicate key error.

user_723210 
UPDATE customers SET first_name = TRIM (TRAILING FROM first_name ) where id = 1

For example, if you want to remove spaces from the beginning of a string, you use the following syntax:

TRIM(LEADING FROM string) The following syntax of the TRIM() function removes all spaces from the end of a string.

TRIM(TRAILING FROM string) And to remove all spaces at the beginning and ending of a string, you use the following syntax:

TRIM(BOTH FROM string)

user_67608830
Related