您可以使用正则表达式来解决这个问题，只要更改代码始终是三个字母后跟一个或多个数字后跟三个字母。

regexp_match(change, 'p.(\D{3})(\d+)(\D{3})')

它返回一个数组，该数组可用于连接到查找表，然后重建缩短的代码。

with split as (
  select *, 
         regexp_match(change, 'p.(\D{3})(\d+)(\D{3})') as parts
    from changes
)
select s.*, 
       concat('p.', 
              coalesce(x1.one_letters, '?'), 
              parts[2], 
              coalesce(x2.one_letters, '?') 
       ) as encoded_change
  from split s
  left join xlate x1 on x1.three_letters = s.parts[1]
  left join xlate x2 on x2.three_letters = s.parts[3];

您的字符串的格式非常特殊。前缀看起来是固定长度的。接着是三个字符，一个数字（大概是一个位置），然后是另外三个字符。
如果总是这样的话，你不需要任何真正复杂的机器来替换。您可以只使用字符串操作：

with replacements as (
      select 'Thr' as three_letters, 'T' as one_letter union all
      select 'Ala' as three_letters, 'A' as one_letter
     ) 
select v.*, 
       left(mutation, 14) || r1.one_letter || replace(substr(mutation, 18), r2.three_letters, r2.one_letter)
from (values ('NP_006209.2:p.Thr1025Ala')) v(mutation) left join
     replacements r1
     on r1.three_letters = substr(mutation, 15, 3) left join
     replacements r2
     on r2.three_letters = right(mutation, 3);

实际上，我建议您更改数据结构，这样值就不会全部编码在一个字符串中。将结果放在多个列中：
name from_amino_acid to_amino_acid position 事实上，我不知道在那之前发生了什么 : ，也不知道 p. 这很重要。您可能还需要将其拆分为多个列。您可以使用这样的逻辑来拆分字符串：

select split_part(mutation, ':', 1) as name,
       substring(split_part(mutation, ':', 2), 3, 3) as from_amino_acid,
       (regexp_matches(split_part(mutation, ':', 2), '[0-9]+'))[1] as position,
       right(mutation, 3) as to_amino_acid
from (values ('NP_006209.2:p.Thr1025Ala')) v(mutation);

这将简化sql，也可能简化分析。

@mike organek提出的另一种解决方案是创建一个短函数来为您执行此转换。
数据样本：

CREATE TEMPORARY TABLE map (three_letters text, one_letters text);
INSERT INTO map 
VALUES ('Val','V'),('Glu','E'),('Thr','T'),('Ala','A');

功能：

CREATE OR REPLACE FUNCTION change_amino_acid(text)
RETURNS TEXT AS $BODY$
DECLARE i RECORD; acid TEXT;
BEGIN
acid := trim((string_to_array($1, ':p.'))[2]);
FOR i IN SELECT * FROM map 
  WHERE three_letters = ANY(regexp_split_to_array(acid, '\d+'))
LOOP
  acid := replace(acid,i.three_letters,i.one_letters);
END LOOP;
RETURN 'p.'||acid;
END; $BODY$ LANGUAGE plpgsql;

如何调用函数：

SELECT 
  change_amino_acid('NP_006209.2:p.Thr1025Ala'),
  change_amino_acid('NP_004324.2:p.Val600Glu');

 change_amino_acid | change_amino_acid 
-------------------+-------------------
 p.T1025A          | p.V600E

在那之后你需要做的就是 UPDATE 使用函数创建表

UPDATE my_table 
SET newcolum = change_amino_acid(long_amino_acid);