Thread

1. Removing terminal period from varchar string in table column

   Rich Shepard <rshepard@appl-ecosys.com> — 2025-07-15T17:30:11Z

   I want to remove the terminal period '.' from the varchar strings in the
   'company_name' column in all rows with that period in the companies table.
   
   I've looked at trim(), translate(), "substr(company_name 1,
   length(compan_name) - 1)", and a couple of other functions and am unsure how
   best to do this without corrupting the database table.
   
   Advice needed.
   
   TIA,
   
   Rich
   
   
   
   
   

2. Re: Removing terminal period from varchar string in table column

   Jeff Ross <jross@openvistas.net> — 2025-07-15T17:43:17Z

   On 7/15/25 11:30, Rich Shepard wrote:
   
   > I want to remove the terminal period '.' from the varchar strings in the
   > 'company_name' column in all rows with that period in the companies 
   > table.
   >
   > I've looked at trim(), translate(), "substr(company_name 1,
   > length(compan_name) - 1)", and a couple of other functions and am 
   > unsure how
   > best to do this without corrupting the database table.
   >
   > Advice needed.
   >
   > TIA,
   >
   > Rich
   >
   >
   >
   How about
   
   test:
   
        select company_name, replace(company_name,'.','') from companies;
   
   update:
   
        update companies set company_name = replace(company_name,'.','') 
   where company_name like '%.';
   
   ?
   
   Jeff
   

3. Re: Removing terminal period from varchar string in table column

   Thom Brown <thom@linux.com> — 2025-07-15T17:46:07Z

   On Tue, 15 Jul 2025, 18:30 Rich Shepard, <rshepard@appl-ecosys.com> wrote:
   
   > I want to remove the terminal period '.' from the varchar strings in the
   > 'company_name' column in all rows with that period in the companies table.
   >
   > I've looked at trim(), translate(), "substr(company_name 1,
   > length(compan_name) - 1)", and a couple of other functions and am unsure
   > how
   > best to do this without corrupting the database table.
   >
   
   There are various options, but perhaps just use rtrim.
   
   rtrim(company_name, '.')
   
   https://www.postgresql.org/docs/current/functions-string.html
   
   Thom
   

4. Re: Removing terminal period from varchar string in table column

   Rich Shepard <rshepard@appl-ecosys.com> — 2025-07-15T17:59:27Z

   On Tue, 15 Jul 2025, Thom Brown wrote:
   
   > There are various options, but perhaps just use rtrim.
   > rtrim(company_name, '.')
   
   Thom,
   
   I looked at rtrim() but didn't see where to specify the table name. Would it
   be `select * from table companies rtrim(company_name, '.')'?
   
   Thanks,
   
   Rich
   
   
   
   

5. Re: Removing terminal period from varchar string in table column

   Rich Shepard <rshepard@appl-ecosys.com> — 2025-07-15T18:02:14Z

   On Tue, 15 Jul 2025, Jeff Ross wrote:
   
   > How about
   >
   > test:
   >     select company_name, replace(company_name,'.','') from companies;
   >
   > update:
   >     update companies set company_name = replace(company_name,'.','') where 
   > company_name like '%.';
   
   Jeff,
   
   These contain the table and column names I didn't see in web page examples.
   Using update looks better to me.
   
   Many thanks,
   
   Rich
   
   
   
   

6. Re: Removing terminal period from varchar string in table column

   Thom Brown <thom@linux.com> — 2025-07-15T18:19:18Z

   On Tue, 15 Jul 2025, 18:59 Rich Shepard, <rshepard@appl-ecosys.com> wrote:
   
   > On Tue, 15 Jul 2025, Thom Brown wrote:
   >
   > > There are various options, but perhaps just use rtrim.
   > > rtrim(company_name, '.')
   >
   > Thom,
   >
   > I looked at rtrim() but didn't see where to specify the table name. Would
   > it
   > be `select * from table companies rtrim(company_name, '.')'?
   >
   
   UPDATE companies
   SET company_name = rtrim(company_name, '.')
   WHERE company_name != rtrim(company_name, '.');
   
   Thom
   
   >
   

7. Re: Removing terminal period from varchar string in table column

   Rich Shepard <rshepard@appl-ecosys.com> — 2025-07-15T20:15:11Z

   On Tue, 15 Jul 2025, Thom Brown wrote:
   
   > UPDATE companies
   > SET company_name = rtrim(company_name, '.')
   > WHERE company_name != rtrim(company_name, '.');
   
   Thom,
   
   That makes sense. The web pages I read assumed I knew to use the UPDATE
   command. As this was the first time I needed to clean column content I
   didn't assume that update was the appropriate mechanism. Now I do.
   
   Thanks,
   
   Rich