Re: Help : Removal of leading spaces in all the columns of a table

Hello

On 2020-02-12 13:42, srikkanth wrote:
> Hi Team,
>
> How can i write the syntax to remove the leading spaces on a table for
> all the columns.
>
> Also, want to know how to do the all words of all the columns in
> capital along with removing of leading\excessive\trailing spaces at a
> time.
>
> Can you please help me out, let me know in case of any inputs.

You may combine existing functions:

CREATE TABLE test (txt TEXT);

INSERT INTO test VALUES ('abc'),(' abc '),(' a b c '),('a b
c');

SELECT * FROM test;
txt
----------------
abc
abc
a b c
a b c
(4 rows)

Now, assuming that "excessive" spaces means that there must be at most
one between words:

SELECT regexp_replace(upper(trim(txt)),'[ ]{2,}', '', 'g') FROM test;
regexp_replace
----------------
ABC
ABC
AB C
A B C
(4 rows)

first you remove all leading and trailing spaces (trim).
Then you transform all letters to upper case (upper).
Finally you remove all spaces but one between the words (regexp_replace
with '[ ]{2,}' meaning 2 or more spaces to be replaced with empty string
'' for all occurrences in the string. 'g' means global).

The order of the calls is not really relevant for the result.

Use UPDATE test SET txt instead of a select if you want to update your
table at once.
I usually prefer to see the result before I act on the data ;-)

Bye
Charles

> Thanks,
>
> Srikanth B

--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Spitzackerstrasse 9
CH - 8057 Zürich

http://www.swisspug.org

+---------------------------+
| ____ ______ ___ |
| / )/ \/ \ |
| ( / __ _\ ) |
| \ (/ o) ( o) ) |
| \_ (_ ) \ ) _/ |
| \ /\_/ \)/ |
| \/ <//| |\\> |
| _| | |
| \|_/ |
| |
| Swiss PGDay 2020 |
| 18/19.06.2020 |
| HSR Rapperswil |
| |
+---------------------------+