How to trim suffix from a text column?

(1) By 6kEs4Majrd on 2022-03-29 01:44:42 [link] [source]

Suppose that I have a text column, I want to remove a suffix in this column.

For example, I may have strings like "Asuf", "Bsuf", "dafasuf" ..., I want to update the database so that they become "A", "B", "dafa" ...

Could anybody let me know what is the most efficient way to perform this change? Thanks.

(2.2) By Keith Medcalf (kmedcalf) on 2022-03-30 01:16:53 edited from 2.1 in reply to 1 [link] [source]

For the column field in table table, where the suffix string is bound to parameter 1, perhaps the following:

  with u
    as (
        select ?1 as substring,
               length(?1) as length
       )
update "table"
   set field = substr(field, 1, length(field)-u.length)
  from u
 where substring(field, -u.length) == u.substring
;

The substring and its length are in a CTE in order to "save" the execution of length(?1) for each row in "table", and during computation of the update. This will be important for non-toy data sizes.

(3.2) By Keith Medcalf (kmedcalf) on 2022-03-30 01:12:25 edited from 3.1 in reply to 1 [source]

You could also do this if you wanted to create a table of suffixes and run them all at once.

create table "table"
(
    field       text not null
);
create table suffixes
(
    suffix      text not null,
    length      integer as (length(suffix)) stored
);
update "table"
   set field = substr(field, 1, length(field) - suffixes.length)
  from suffixes
 where substr(field, -suffixes.length) == suffixes.suffix
;

Note that if you have two entries, one of which is a "trailing suffix" of the other, then the results are undefined and you may find yourself extinct in the heat-death of the multiverse.

(4) By anonymous on 2022-03-29 08:58:08 in reply to 1 [link] [source]

You might use REPLACE to trim the values:

WITH myTable (suf)
AS (
	VALUES ('Asuf'),
		('Bsuf'),
		('datasuf')
	)
SELECT replace(suf, 'suf', '') AS TrimmedValue
FROM myTable;

TrimmedValue
------------
A
B
data

(5) By Gunter Hick (gunter_hick) on 2022-03-29 10:51:50 in reply to 4 [link] [source]

This will not work for 'sufsuf'.