使用SQL Oracle将另一张表的数据替换一些变量。

huangapple
go评论82阅读模式
英文:

Replace some variables by data of another table in sql oracle

问题

我有一个包含两列的表格

类型 文本
A 这是一些文本，包括%1和%2
B 这是另一个步骤，包括%1

在一个翻译表中，我有变量%X的含义，如下所示

类型 变量 描述
A %1 #人员1#
A %2 #人员2#
B %1 #经理#

我想要在我的第一个表中用描述替换所有变量，所以结果应该如下所示：

类型 文本
A 这是一些文本，包括#人员1#和#人员2#
B 这是另一个步骤，包括#经理#

我尝试过使用替换，但没有弄清楚如何让它起作用。

英文:

I have a table with two columns

type TXT
A this is some text for %1 and %2
B this is another step for %1

in a translation table I have the signification of the variables %X that looks like

Type variable descr
A %1 #person1#
A %2 #person2#
B %1 #manager#

I want to replace in my first table all the variables by the description, so the result has to looks like this:

type TXT
A this is some text for #person1# and #person2#
B this is another step for #manager#

I tried with a replace, but I didn't figured out how to make it work

答案1

得分: 1

无法提供代码翻译。

英文:

To replace all variables you could use a recursive algorithm:

  1. with data(typ, txt) as (
  2.     select 'A', 'this is some text for %1 and %2' from dual union all
  3.     select 'B',	'this is another step for %1' from dual 
  4. ),
  5. translations(typ, var, description) as (
  6.    select 'A', '%1', '#person1#' from dual union all
  7.    select 'A', '%2', '#person2#' from dual union all
  8.    select 'B', '%1', '#manager#' from dual -- union all
  9. ),
  10. rtranslations(typ, var, description,rn) as (
  11.     select t.*, row_number() over(partition by typ order by var) as rn
  12.         from translations t
  13. ),
  14. replacecte(typ, txt, replaced_txt, rn) as (
  15.     select d.typ, d.txt, replace(d.txt, t.var, t.description), t.rn
  16.     from data d
  17.     join rtranslations t on t.typ = d.typ
  18.     where t.rn = 1
  20.     union all
  22.     select r.typ, r.txt, replace(r.replaced_txt, t.var, t.description), t.rn
  23.     from replacecte r
  24.     join rtranslations t on t.typ = r.typ and t.rn = r.rn + 1
  25. )
  26. select r.typ, r.txt, replaced_txt from replacecte r
  27. where rn = length(txt) - length(replace(txt,'%',''))
  28. ;

答案2

得分: 1

以下是翻译好的代码部分：

  1. 你可以不使用递归来实现这种方式。`data` 和 `descr` 当然只是你表格的模拟，你不需要任何 `WITH` 子句。这种方法使用以下步骤：(1) 将句子分解成单词，(2) 使用这些单词与你的描述表进行外连接，用描述值替换任何匹配项，(3) 使用 `LISTAGG` 将单词重新组合成句子。
  3.     WITH data AS(SELECT 'A' type, 'this is some text for %1 and %2' txt FROM dual
  4.                   UNION ALL
  5.                  SELECT 'B' type, 'this is another step for %1' txt FROM dual
  6.                  ),
  7.          descr AS (SELECT 'A' type, '%1' variable,'#person1#' description FROM dual
  8.                    UNION ALL
  9.                    SELECT 'A' type, '%2' variable,'#person2#' description FROM dual
  10.                    UNION ALL
  11.                    SELECT 'B' type, '%1' variable,'#manager#' description FROM dual)
  12.     SELECT type,
  13.            LISTAGG(new_word,' ') WITHIN GROUP (ORDER BY seq) txt
  14.       FROM (SELECT x.type,
  15.                    NVL(descr.description,x.word) new_word,
  16.                    seq
  17.               FROM (SELECT type,SUBSTR(' '||txt,INSTR(' '||txt,' ',1,seq)+1,INSTR(' '||txt||' ',' ',1,seq+1) - (INSTR(' '||txt,' ',1,seq)+1)) word,seq
  18.                       FROM data,
  19.                            (SELECT ROWNUM seq FROM dual CONNECT BY LEVEL <= 50) x) x,
  20.                    descr
  21.              WHERE x.type = descr.type(+)
  22.                AND x.word = descr.variable(+))
  23.      GROUP BY type

希望这能帮助你！

英文:

You can also do it this way without recursion. data and descr are of course just mock ups for your tables, you would not need any WITH clauses. This method uses the steps (1) break up the sentences into words, (2) outer join using those words to your description table, replacing any matches with the description values, (3) reassemble the words back into sentences using LISTAGG.

  1. WITH data AS(SELECT &#39;A&#39; type, &#39;this is some text for %1 and %2&#39; txt FROM dual
  2.               UNION ALL
  3.              SELECT &#39;B&#39; type, &#39;this is another step for %1&#39; txt FROM dual
  4.              ),
  5.      descr AS (SELECT &#39;A&#39; type, &#39;%1&#39; variable,&#39;#person1#&#39; description FROM dual
  6.                UNION ALL
  7.                SELECT &#39;A&#39; type, &#39;%2&#39; variable,&#39;#person2#&#39; description FROM dual
  8.                UNION ALL
  9.                SELECT &#39;B&#39; type, &#39;%1&#39; variable,&#39;#manager#&#39; description FROM dual)
  10. SELECT type,
  11.        LISTAGG(new_word,&#39; &#39;) WITHIN GROUP (ORDER BY seq) txt
  12.   FROM (SELECT x.type,
  13.                NVL(descr.description,x.word) new_word,
  14.                seq
  15.           FROM (SELECT type,SUBSTR(&#39; &#39;||txt,INSTR(&#39; &#39;||txt,&#39; &#39;,1,seq)+1,INSTR(&#39; &#39;||txt||&#39; &#39;,&#39; &#39;,1,seq+1) - (INSTR(&#39; &#39;||txt,&#39; &#39;,1,seq)+1)) word,seq
  16.                   FROM data,
  17.                        (SELECT ROWNUM seq FROM dual CONNECT BY LEVEL &lt;= 50) x) x,
  18.                descr
  19.          WHERE x.type = descr.type(+)
  20.            AND x.word = descr.variable(+))
  21.  GROUP BY type

答案3

得分: 0

你可以使用 PIVOT 将行中的变量值转换为列（将所有变量在同一行中与文本一起），然后根据变量值的数量执行多次替换：

  1. SELECT      t.A_TYPE, 
  2.             CASE  WHEN d.V3 Is Not Null  THEN REPLACE(REPLACE(REPLACE(t.TXT, '%1', d.V1), '%2', d.V2), '%3', d.V3)
  3.                   WHEN d.V2 Is Not Null  THEN REPLACE(REPLACE(t.TXT, '%1', d.V1), '%2', d.V2)
  4.                   WHEN d.V1 Is Not Null  THEN REPLACE(t.TXT, '%1', d.V1)
  5.             ELSE  t.TXT
  6.             END "TXT"
  7. FROM        tbl t
  8. INNER JOIN  ( SELECT  *
  9.               FROM    ( Select A_TYPE, VAR, DESCRIPTION FROM descr  )
  10.                       PIVOT ( MAX(DESCRIPTION) For VAR IN('%1' "V1", '%2' "V2", '%3' "V3") )
  11.             ) d ON(d.A_TYPE = t.A_TYPE)

带有示例数据的部分如下：

  1. WITH 
  2.     tbl (A_TYPE, TXT) AS
  3.         (
  4.             Select 'A', 'this is some text for %1 and %2'  From Dual Union All
  5.             Select 'B', 'this is another step for %1'      From dual
  6.         ),
  7.      descr (A_TYPE, VAR, DESCRIPTION) AS 
  8.         (
  9.             Select 'A', '%1', '#person1#'  From Dual UNION ALL
  10.             Select 'A', '%2', '#person2#'  From Dual UNION ALL
  11.             Select 'B', '%1', '#manager#'  From Dual
  12.         )

结果应如下所示：

  1. A_TYPE TXT                                            
  2. ------ -----------------------------------------------
  3. A      this is some text for #person1# and #person2#  
  4. B      this is another step for #manager#
英文:

You could use PIVOT to get the var values from rows into columns (geting all vars in the same row with text) and then do multiple replaces depending on number of var values:

  1. SELECT      t.A_TYPE, 
  2.             CASE  WHEN d.V3 Is Not Null  THEN REPLACE(REPLACE(REPLACE(t.TXT, &#39;%1&#39;, d.V1), &#39;%2&#39;, d.V2), &#39;%3&#39;, d.V3)
  3.                   WHEN d.V2 Is Not Null  THEN REPLACE(REPLACE(t.TXT, &#39;%1&#39;, d.V1), &#39;%2&#39;, d.V2)
  4.                   WHEN d.V1 Is Not Null  THEN REPLACE(t.TXT, &#39;%1&#39;, d.V1)
  5.             ELSE  t.TXT
  6.             END &quot;TXT&quot;
  7. FROM        tbl t
  8. INNER JOIN  ( SELECT  *
  9.               FROM    ( Select A_TYPE, VAR, DESCRIPTION FROM descr  )
  10.                       PIVOT ( MAX(DESCRIPTION) For VAR IN(&#39;%1&#39; &quot;V1&quot;, &#39;%2&#39; &quot;V2&quot;, &#39;%&#39; &quot;V3&quot;) )
  11.             ) d ON(d.A_TYPE = t.A_TYPE)

With sample data as:

  1. WITH 
  2.     tbl (A_TYPE, TXT) AS
  3.         (
  4.             Select &#39;A&#39;, &#39;this is some text for %1 and %2&#39;  From Dual Union All
  5.             Select &#39;B&#39;, &#39;this is another step for %1&#39;      From dual
  6.         ),
  7.      descr (A_TYPE, VAR, DESCRIPTION) AS 
  8.         (
  9.             Select &#39;A&#39;, &#39;%1&#39;, &#39;#person1#&#39;  From Dual UNION ALL
  10.             Select &#39;A&#39;, &#39;%2&#39;, &#39;#person2#&#39;  From Dual UNION ALL
  11.             Select &#39;B&#39;, &#39;%1&#39;, &#39;#manager#&#39;  From Dual
  12.         )

... the result should be

  1. A_TYPE TXT                                            
  2. ------ -----------------------------------------------
  3. A      this is some text for #person1# and #person2#  
  4. B      this is another step for #manager#

huangapple
  • 本文由 发表于 2023年2月14日 21:50:10
  • 转载请务必保留本文链接：https://go.coder-hub.com/75448797.html
  • oracle
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定