1. 首页
  2. 问题
  3. oracle SQL 中的格式化字符串，用字符串末尾给出的变量名替换占位符
小编典典

oracle SQL 中的格式化字符串，用字符串末尾给出的变量名替换占位符

sql

我正在使用 Oracle DB 并以 blob 格式存储一些 XML 数据，XML 具有：

a) 一个公式（例如%1 - %2, 或%2||'-'||%1）

b) 适合公式的变量的分隔列表（例如SALE_Q1| SALE_Q2 或YEAR| MONTH）

我已经设法将这些数据提取到 2 个不同的列中（如果需要也可以合并到 1 个列中），我需要做的是将输出列作为变量叠加到占位符上。

（例如。 SALE_Q1 - SALE_Q2 ，或MONTH||'-'||YEAR）

对此还有一些注意事项，例如：

我不知道每个公式有多少个变量，
变量不会总是以与分隔列表相同的顺序在公式中使用（参见 eg.2）
我们可以考虑来自以下查询的数据：SELECT formula || ', ' || columns_used FROM data_table;对于输入字符串和我当前获得的输出如下：

%1||'-'||%2||%3, SITE_NO| SITE_NAME| COUNTRY
0.1 * %1, WIND_RES
%1, TOTAL
CASE WHEN LENGTH(%1) < 8 THEN NULL ELSE TO_DATE(%1,'yyyymmdd')END, MIN_DATE
%1(+)=%3 and %2(+)=%4, ABC| LMN| PQR| XYZ

我对 SQL 很陌生，这个要求超出了我的脑海，任何帮助将不胜感激。我需要一个 SQL 解决方案，因为 PL/SQL 解决方案在我的要求中不可行（此脚本将从一个数据库中提取数据并定期将其提供给另一个存储库）

我看过一些关于 XML 表、模型或递归正则表达式的文章，但我不确定如何使用这些。我也在StackOverflow 上看到过这个问题，但我的要求与那个有点不同，也有点棘手。将公式字符串和变量放入同一个字符串中以进行类似问题的处理也是一种合理的途径。任何可以用 SQL 查询编写的解决方案都会非常有帮助。

还有一些例子供您考虑：

"%1||'-'||%2||%3, SITE_NO| SITE_NAME| COUNTRY" => "SITE_NO||'-'||SITE_NAME||COUNTRY"

"0.1 * %1, WIND_RES" => "0.1 * WIND_RES"

"%1, TOTAL" => "TOTAL"

"CASE WHEN LENGTH(%1) < 8 THEN NULL ELSE TO_DATE(%1,'yyyymmdd')END, MIN_DATE" => "CASE WHEN LENGTH(MIN_DATE) < 8 THEN NULL ELSE TO_DATE(MIN_DATE,'yyyymmdd')END"

"%1(+)=%3 and %2(+)=%4, ABC| LMN| PQR| XYZ" => "ABC(+)=PQR and LMN(+)=XYZ"

阅读 203

收藏
2021-05-30

共1个答案

小编典典

我假设最后一个逗号是模板字符串和分隔术语之间的分隔符。您可以使用递归子查询分解子句和简单的字符串函数：

WITH terms ( value, terms, num_terms ) AS (
  SELECT SUBSTR( value, 1, INSTR( value, ', ', -1 ) - 1 ),
         SUBSTR( value, INSTR( value, ', ', -1 ) + 2 ),
         REGEXP_COUNT(
           SUBSTR( value, INSTR( value, ', ', -1 ) + 2 ),
           '.+?(\| |$)'
         )
  FROM   table_name
),
term_bounds ( rn, value, terms, start_pos, lvl ) AS (
  SELECT ROWNUM,
         REPLACE(
           value,
           '%' || num_terms,
           CASE num_terms
           WHEN 1
           THEN terms
           ELSE SUBSTR( terms, INSTR( terms, '| ', 1, num_terms - 1 ) + 2 )
           END
         ),
         terms,
         CASE
         WHEN num_terms > 1
         THEN INSTR( terms, '| ', 1, num_terms - 1 )
         ELSE 1
         END,
         num_terms
  FROM   terms
UNION ALL
  SELECT rn,
         REPLACE(
           value,
           '%' || (lvl - 1),
           CASE lvl - 1
           WHEN 1
           THEN SUBSTR( terms, 1, start_pos - 1 )
           ELSE SUBSTR(
                  terms,
                  INSTR( terms, '| ', 1, lvl - 2 ) + 2,
                  start_pos - INSTR( terms, '| ', 1, lvl - 2 ) - 2
                )
           END
         ),
         terms,
         CASE
         WHEN lvl > 2
         THEN INSTR( terms, '| ', 1, lvl - 2 )
         ELSE 1
         END,
         lvl - 1
  FROM   term_bounds
  WHERE  lvl > 1
)
SEARCH DEPTH FIRST BY rn SET rn_order
SELECT value
FROM   term_bounds
WHERE  lvl = 1;

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT '%1||'-'||%2||%3, SITE_NO| SITE_NAME| COUNTRY' FROM DUAL UNION ALL
SELECT '0.1 * %1, WIND_RES' FROM DUAL UNION ALL
SELECT '%1, TOTAL' FROM DUAL UNION ALL
SELECT 'CASE WHEN LENGTH(%1) < 8 THEN NULL ELSE TO_DATE(%1,'yyyymmdd')END, MIN_DATE' FROM DUAL UNION ALL
SELECT '%1(+)=%3 and %2(+)=%4, ABC| LMN| PQR| XYZ' FROM DUAL UNION ALL
SELECT '%1, %2, %3, %4, %5, %6, %7, %8, %9, %10, %11, ONE| TWO| THREE| FOUR| FIVE| SIX| SEVEN| EIGHT| NINE| TEN| ELEVEN' FROM DUAL UNION ALL
SELECT '%%%%%%%7, HELLO| 1| 2| 3| 4| 5| 6' FROM DUAL

Outputs:

| VALUE                                                                                      |
| :----------------------------------------------------------------------------------------- |
| SITE_NO||'-'||SITE_NAME||COUNTRY                                                 |
| 0.1 * WIND_RES                                                                             |
| TOTAL                                                                                      |
| CASE WHEN LENGTH(MIN_DATE) < 8 THEN NULL ELSE TO_DATE(MIN_DATE,'yyyymmdd')END |
| ABC(+)=PQR and LMN(+)=XYZ                                                                  |
| ONE, TWO, THREE, FOUR, FIVE, SIX, SEVEN, EIGHT, NINE, TEN, ELEVEN                          |
| HELLO
2021-05-30