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

CCing the list.

Hello

On 2020-02-12 14:30, Pete Yunker wrote:
> Shouldn’t the replacement string in regexp_replace be a single space
> instead of a 0-length string?

Yes, correct.

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

The 3rd row was not modified correctly in my previous example.
Thank you for pointing out.

And to get back to the OP I saw that he wants a statement for all
columns - I assume of a table.

In this case it is possible to generate the statement using the system
catalogs.

I modified the test table to illustrate this.

\d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
txt | text | | |
i | integer | | |
txt2 | character varying | | |

select * from test;
txt | i | txt2
----------------+---+-----------
abc | 1 | de f
abc | 2 | d e f
a b c | 3 | d e f
a b c | 4 | def
(4 rows)

Now create the statement replacing 'test' with the name of the table
that must be processed.

SELECT format($$UPDATE %I.%I SET (%s) = (%s)$$,
c.relnamespace::REGNAMESPACE,
c.relname,
string_agg(a.attname, ', '),
string_agg(format($$regexp_replace(upper(trim(%s)),'[
]{2,}', ' ', 'g')$$, a.attname), ', '))
FROM pg_catalog.pg_attribute a,
pg_catalog.pg_class c
WHERE a.attrelid = c.oid
AND a.atttypid::regtype IN ('text','varchar')
AND a.attnum > 0
AND NOT a.attisdropped
AND c.relname = 'test'
GROUP BY c.relnamespace::regnamespace,
c.relname;

And execute the resulting statement.

UPDATE public.test SET (txt, txt2) = (regexp_replace(upper(trim(txt)),'[
]{2,}', ' ', 'g'), regexp_replace(upper(trim(txt2)),'[ ]{2,}', ' ',
'g'));

Content of the table after the update.

select * from test;
txt | i | txt2
-------+---+-------
ABC | 1 | DE F
ABC | 2 | D E F
A B C | 3 | D E F
A B C | 4 | DEF
(4 rows)

Regards
Charles

>
>> On Feb 12, 2020, at 8:23 AM, Charles Clavadetscher
>> <clavadetscher(at)swisspug(dot)org> wrote:
>>
>> 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 |
| |
+---------------------------+