Re: Help : Removal of leading spaces in all the columns of a table - Mailing list pgsql-general

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