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

Just do a search replace? Anything particular you find difficult? — Wolph, Dec 04 '13 at 13:27
I'd try SELECT regexp_replace(myfield, ' ', '', 'g') FROM mytable; — gino pilotino, Dec 04 '13 at 13:48
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 — LeoSam, Dec 04 '13 at 22:12

score 87 · Answer 1 · edited Sep 23 '20 at 10:29

87

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

edited Sep 23 '20 at 10:29

Vic Seedoubleyew

9,888
6
55
76

answered Dec 04 '13 at 13:28

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 – LeoSam Dec 04 '13 at 22:11
2

@MarcoDonJuan: sorry, you lost me at "space raw". I have no idea what you are talking about. Please create a sample at http://sqlfiddle.com and let us know how the new data should look like. – Dec 04 '13 at 22:23
1

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 – LeoSam Dec 04 '13 at 22:28
1

@LeoSam I tried `suzan_andy` and `mark'bart`, `replace` didn't remove special characters. It works just fine! – ziMtyth Feb 14 '18 at 08:23

score 24 · Answer 2 · answered Feb 28 '18 at 21:09

24

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

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

answered Feb 28 '18 at 21:09

Rasjid Wilcox

659
7
11

score 13 · Answer 3 · answered Jan 05 '19 at 17:55

13

Just use the simple code

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

or

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

to remove any white space from the string

answered Jan 05 '19 at 17:55

Bassam Faramawi

162
1
5

3

`\s` has no special meaning for `replace()` the second statement removes a backslash followed by the character `s` from the string. – Sep 23 '20 at 10:30
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](https://stackoverflow.com/a/20376672/4742889). (3) `REPLACE()` doesn't allow REGEX as you suggest by using `\s` – andschar Aug 09 '23 at 11:30

score 6 · Answer 4 · answered Apr 15 '20 at 14:28

6

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

answered Apr 15 '20 at 14:28

Gudmo

61
1
2

Would that be the same as `UPDATE users SET fullname = REPLACE(fullname, ' ', '') WHERE fullname not like '% %';` – Jeremy Thompson Nov 11 '20 at 02:47
@jeremy : it should be `UPDATE users SET fullname = REPLACE(fullname, ' ', '') WHERE fullname like '% %';` – Pablo Guerrero Jan 24 '21 at 13:13

score 4 · Answer 5 · answered Feb 09 '17 at 23:25

4

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"}

answered Feb 09 '17 at 23:25

Westy92

19,087
4
72
54

score 2 · Answer 6 · answered Nov 07 '19 at 00:31

2

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.

answered Nov 07 '19 at 00:31

Dennis

56,821
26
143
139

PO wants to remove all white spaces, however, `trim` only remove space from start, end or both. Not remove space in the string. – nngeek Nov 21 '19 at 08:10

score 1 · Answer 7 · answered Oct 23 '19 at 10:35

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)

It will not convert `Adam Noel` into `AdamNoel` – fallais Aug 27 '20 at 09:00 — fallais, Aug 27 '20 at 09:00

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

7 Answers7

Linked