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

Question

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

Spirt

Just do a search replace? Anything particular you find difficult? – Summons

I'd try SELECT regexp_replace(myfield, ' ', '', 'g') FROM mytable; – Caracara

i did try it but the update amount of raw was more than what i what i have with space i mean space raw is 30k but the amount update was 45k , can we limite the update just for the space and ignore any other special charters – Marco DonJuan just now edit – Spirt

Answer 1

update users
  set fullname = replace(fullname, ' ', '');

Protist

i did try it but the update amount of raw was more than what i what i have with space i mean space raw is 30k but the amount update was 45k , can we limite the update just for the space and ignore any other special charters – Spirt

@MarcoDonJuan: sorry, you lost me at "space raw". I have no idea what you are talking about. Please create a sample at sqlfiddle.com and let us know how the new data should look like. – Protist

ok , ive fullname coulmn data like "Adam John" your statment removed space and its become "AdamJohn" which is pretty much what i want , but its also removed spiceal charters from other names like "mark'bart" "suzan_andy" etc – Spirt

@Spirt I tried suzan_andy and mark'bart, replace didn't remove special characters. It works just fine! – Sin

Answer 2

To remove all whitespace (not just space characters) one can use:

update users set fullname = regexp_replace(fullname, '\s', '', 'g');
commit;

Mont

Answer 3

Just use the simple code

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

or

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

to remove any white space from the string

Keyboard

\s has no special meaning for replace() the second statement removes a backslash followed by the character s from the string. – Protist

Sorry, but this answer is partly wrong and doesn't show anything, which has not already been shown in previous answers: (1) There is no difference between REPLACE() and Replace() (2) REPLACE() works, as you suggest, but this has already been shown in 2013 by user330315. (3) REPLACE() doesn't allow REGEX as you suggest by using \s – Belfry

Answer 4

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

Fondly

Would that be the same as UPDATE users SET fullname = REPLACE(fullname, ' ', '') WHERE fullname not like '% %'; – Banda

@jeremy : it should be UPDATE users SET fullname = REPLACE(fullname, ' ', '') WHERE fullname like '% %'; – Bludgeon

Answer 5

If it's a text[] column, you can do something like this:

UPDATE users SET pets = string_to_array(replace(array_to_string(pets, ';'), ' ', ''), ';');

Before: {"Big Dog", "Small Cat"}

After: {"BigDog", "SmallCat"}

Tapdance

Answer 6

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.

Bacchanal

PO wants to remove all white spaces, however, trim only remove space from start, end or both. Not remove space in the string. – Expansionism

Answer 7

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)

Zealous

It will not convert Adam Noel into AdamNoel – Bingaman