postgresql 如何在更新查询中从 Postgres 数据库中的字段中删除所有空格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20376579/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I remove all spaces from a field in a Postgres database in an update query?
提问by LeoSam
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 usersand in the column fullnamesome values look like 'Adam Noel'. I want to remove the space so that the new value is 'AdamNoel'
我的表被调用，users并且列中的fullname一些值看起来像'Adam Noel'。我想删除空格，以便新值是'AdamNoel'
I have like 30k rows
我有 30k 行
回答by a_horse_with_no_name
update users
set fullname = replace(fullname, ' ', '');
commit;
回答by Bassam Faramawi
Just use the simple code
只需使用简单的代码
REPLACE('some_string', ' ', '')
or
或者
Replace('some_string', '\s', '')
to remove any white space from the string
从字符串中删除任何空格
回答by Rasjid Wilcox
To remove all whitespace (not just space characters) one can use:
要删除所有空格（不仅仅是空格字符），可以使用：
update users set fullname = regexp_replace(fullname, '\s', '', 'g');
commit;
回答by Westy92
If it's a text[]column, you can do something like this:
如果它是一text[]列，您可以执行以下操作：
UPDATE users SET pets = string_to_array(replace(array_to_string(pets, ';'), ' ', ''), ';');
Before: {"Big Dog", "Small Cat"}
前： {"Big Dog", "Small Cat"}
After: {"BigDog", "SmallCat"}
后： {"BigDog", "SmallCat"}
回答by Gudmo
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
又快又脏
回答by vipinlalrv
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(LEADING FROM string) TRIM() 函数的以下语法从字符串的末尾删除所有空格。
TRIM(TRAILING FROM string) And to remove all spaces at the beginning and ending of a string, you use the following syntax:
TRIM(TRAILING FROM string) 要删除字符串开头和结尾的所有空格，请使用以下语法：
TRIM(BOTH FROM string)
修剪（来自字符串）
回答by Dennis
Can perform an update all with the trimfunction.
都可以用该trim功能进行更新。
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.
（可选）添加一个子句以仅更新需要它的记录，而不是所有记录，但会使用更多 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.
如果表在被修剪的值上有唯一索引，则可能会出现重复键错误。
