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