Обсуждение: 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
On Feb 12, 2020, at 7:09 AM, Susan Hurst <susan.hurst@brookhurstdata.com> wrote:
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-3261On 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
For larger production tables I would stream the converted data through COPY into a new table of identical shape, build indeces then rename both and truncate/drop old table.