regexp_replace将一个列表替换为另一个列表SQL oracle

我如何用相应的单个字符串替换这些列中的每一列中的3个字母串。

('Ala' = 'A','Arg' = 'R','Glu' = 'E', 'Lys' = 'K', 'Ser' = 'S', 'Thr' = 'T', 'Ile' = 'I', 'Leu' = 'L', 'Met' = 'M')

| 色谱柱A|
| - -----|
| Arg790Met|
| Gly724Ser|
| Leu858Arg|
| Lys768Ile|
| Thr790Met|
| Glu746Ala750del|
所以我最后得到：
| 色谱柱A|
| - -----|
| R790M|
| G724S|
| L858R|
| k768I|
| T790M|
| E746A750del|
不幸的是，这并不像第一个字母那么简单，因为并不是所有的短代码都有第一个字母。
我试过regexp_replace，但不知道如何用list替换。我不能分离出主字符串的不同部分，替换它们，然后重新组合，因为它们可能超过11个字符，并且有一些3个字符的元素我想保持不变，比如'del'。
可以选择嵌套regexp_replace函数，但我的完整列表有21个长，所以这看起来很糟糕，我想知道是否有更优雅的方法。

使用递归查询和简单的字符串函数递归遍历子字符串，然后用其缩写替换每个三字符代码（如果存在）：

WITH replacements (columna, spos) AS (
  SELECT columna, 1 FROM table_name
UNION ALL
  SELECT SUBSTR(columna, 1, spos - 1)
         || COALESCE(t.abbr, SUBSTR(columna, spos, 3))
         || SUBSTR(columna, spos + 3),
         spos + NVL2(t.code, LENGTH(t.abbr), 3) + 3
  FROM   replacements r
         LEFT OUTER JOIN translations t
         ON SUBSTR(columna, spos, 3) = t.code
  WHERE  spos < LENGTH(columna)
)
  SEARCH DEPTH FIRST BY columna SET order_id
SELECT columna
FROM   replacements
WHERE  spos >= LENGTH(columna);

其中，对于样本数据：

CREATE TABLE translations (code, abbr) AS
SELECT 'Ala', 'A' FROM DUAL UNION ALL
SELECT 'Arg', 'R' FROM DUAL UNION ALL
SELECT 'Glu', 'E' FROM DUAL UNION ALL
SELECT 'Lys', 'K' FROM DUAL UNION ALL
SELECT 'Ser', 'S' FROM DUAL UNION ALL
SELECT 'Thr', 'T' FROM DUAL UNION ALL
SELECT 'Ile', 'I' FROM DUAL UNION ALL
SELECT 'Leu', 'L' FROM DUAL UNION ALL
SELECT 'Met', 'M' FROM DUAL;

CREATE TABLE table_name (ColumnA) AS
SELECT 'Arg790Met' FROM DUAL UNION ALL
SELECT 'Gly724Ser' FROM DUAL UNION ALL
SELECT 'Leu858Arg' FROM DUAL UNION ALL
SELECT 'Lys768Ile' FROM DUAL UNION ALL
SELECT 'Thr790Met' FROM DUAL UNION ALL
SELECT 'Glu746Ala750del' FROM DUAL;

输出：
| 柱|
| - -----|
| R790M|
| E746A750del|
| Gly724S|
| L858R|
| K768I|
| T790M|
fiddle
如果你可以使用可变长度的数字分隔符，那么你可以使用REGEXP_INSTR来找到每个数字的结尾：

WITH replacements (columna, spos) AS (
  SELECT columna, 1 FROM table_name
UNION ALL
  SELECT SUBSTR(columna, 1, spos - 1)
         || COALESCE(t.abbr, SUBSTR(columna, spos, 3))
         || SUBSTR(columna, spos + 3),
         REGEXP_INSTR(
           columna,
           '\d*',
           spos + 3,
           1,
           1
         ) - NVL2(t.code, 3 - LENGTH(t.abbr), 0)
  FROM   replacements r
         LEFT OUTER JOIN translations t
         ON SUBSTR(columna, spos, 3) = t.code
  WHERE  spos > 0
)
  SEARCH DEPTH FIRST BY columna SET order_id
SELECT columna
FROM   replacements
WHERE  spos <= 0

其中，对于样本数据：

CREATE TABLE translations (code, abbr) AS
SELECT 'Ala', 'A' FROM DUAL UNION ALL
SELECT 'Arg', 'R' FROM DUAL UNION ALL
SELECT 'Glu', 'E' FROM DUAL UNION ALL
SELECT 'Lys', 'K' FROM DUAL UNION ALL
SELECT 'Ser', 'S' FROM DUAL UNION ALL
SELECT 'Thr', 'T' FROM DUAL UNION ALL
SELECT 'Ile', 'I' FROM DUAL UNION ALL
SELECT 'Leu', 'L' FROM DUAL UNION ALL
SELECT 'Met', 'M' FROM DUAL;

CREATE TABLE table_name (ColumnA) AS
SELECT 'Arg790Met' FROM DUAL UNION ALL
SELECT 'Gly724Ser' FROM DUAL UNION ALL
SELECT 'Leu858Arg' FROM DUAL UNION ALL
SELECT 'Lys768Ile' FROM DUAL UNION ALL
SELECT 'Thr790Met' FROM DUAL UNION ALL
SELECT 'Glu746Ala750del' FROM DUAL UNION ALL
SELECT 'Lys1768Ile' FROM DUAL UNION ALL
SELECT 'Lys68Ile' FROM DUAL;

输出：
| 柱|
| - -----|
| R790M|
| E746A750del|
| Gly724S|
| L858R|
| K1768I|
| 公司简介|
| K768I|
| T790M|
fiddle

regexp_replace将一个列表替换为另一个列表SQL oracle

1条答案

相关问题

热门标签

最新问答