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

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.

Thanks,

Srikanth B

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       |
|                           |
+---------------------------+

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@swisspug.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       |
|                           |
+---------------------------+

I once wrote a trigger function to do just what you asked, however, it was a huge drain on performance so I didn't use it for long, so I dropped the trigger.  Hopefully, someone has a more practical approach.  I would be interested in this also.

Sue

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261

On 2020-02-12 06: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.

 

Thanks,

Srikanth B



I once wrote a trigger function to do just what you asked, however, it was a huge drain on performance so I didn't use it for long, so I dropped the trigger.  Hopefully, someone has a more practical approach.  I would be interested in this also.

Sue

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261

On 2020-02-12 06: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