cancel
Showing results for 
Search instead for 
Did you mean: 

Help : removal of leading spaces in all the columns of a table

Level 2 Adventurer

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.

 

Thanks,

Srikanth B

1 REPLY 1
Adventurer

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

For the LEADING and TRAILING space, we can use TRIM() in Postgres.

To remove excessive spaces in the string we can use the PostgreSQL function, but I have checked that it is only replacing the first occurrence of multiple spaces in the string.

 

Example:

postgres=# select * from t1;
                    a                    
-----------------------------------------
    Multiple   spaces   in a   string   
(1 row)

postgres=# SELECT REGEXP_REPLACE(a, '( ){2,}', ' ') FROM t1;
            regexp_replace             
---------------------------------------
  Multiple   spaces   in a   string   
(1 row)

 

I have created a function of using the same "REGEXP_REPLACE" function multiple times to remove the excessive spaces in the columns as below.

create or replace function rm_spaces_instring(c1 text) returns text as $$
begin
for i in 1.. array_length(regexp_split_to_array(c1,'\s'),1) loop
select REGEXP_REPLACE(c1, '( ){2,}', ' ') into c1;
end loop;
return c1;
end;
$$ language plpgsql;

postgres=# UPDATE t1 set a= UPPER(TRIM(rm_spaces_instring(a)));
UPDATE 1
postgres=# SELECT * FROM t1;
a 
-----------------------------
MULTIPLE SPACES IN A STRING
(1 row)

Please note, the nesting of functions may not be a good choice.
I hope it will help.