Menu
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
776
PostgreSQL provides you with LTRIM, RTRIM() and BTRIM functions that are the shorter version of the TRIM() function. The LTRIM() function removes all characters, spaces by default, from the beginning of a string. The RTRIM() function removes all characters, spaces by default, from the end of a string.
In PostgreSQL, the TRIM() function is used to remove the longest string consisting of spaces or any specified character from a string. By default, the TRIM() function removes all spaces (' ') if not specified explicitly.
It is acceptable to use spaces when you are aliasing a column name. However, it is not generally good practice to use spaces when you are aliasing a table name. The alias_name is only valid within the scope of the SQL statement.
BTRIM() function The PostgreSQL btrim function is used to remove the longest string specified in the argument from the start and end of the given string. If no string for removing default space will be removed from leading and trailing side from the string.
update users set fullname = replace(fullname, ' ', ''); To remove all whitespace (not just space characters) one can use:
update users set fullname = regexp_replace(fullname, '\s', '', 'g'); commit; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
