将管道分隔的字符串解析为列? [英] Parsing pipe delimited string into columns?

问题描述

我有一列用管道分隔的值，例如:

I have a column with pipe separated values such as:

'23 | 12.1 | 450 | 30 | 9 | 78 | 82.5 | 92.1 | 120 | 185 | 52 | 11'

'23|12.1| 450|30|9|78|82.5|92.1|120|185|52|11'

我想解析此列以用12个对应列填充表格:month1，month2，month3 ... month12.

I want to parse this column to fill a table with 12 corresponding columns: month1, month2, month3...month12.

所以month1的值为23，month2的值为12.1，依此类推...

So month1 will have the value 23, month2 the value 12.1 etc...

有没有一种方法可以通过循环或分隔符来解析它，而不必每次都使用substr来分隔一个值?

Is there a way to parse it by a loop or delimeter instead of having to separate one value at a time using substr?

谢谢.

推荐答案

您可以使用regexp_substr(10g +):

You can use regexp_substr (10g+):

SQL> SELECT regexp_substr('23|12.1| 450|30|9|', '[^|]+', 1, 1) c1,
  2         regexp_substr('23|12.1| 450|30|9|', '[^|]+', 1, 2) c2,
  3         regexp_substr('23|12.1| 450|30|9|', '[^|]+', 1, 3) c3
  4    FROM dual;

C1 C2   C3
-- ---- ----
23 12.1  450

在PL/SQL中具有循环:

With a loop in PL/SQL:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     p_tsv  VARCHAR2(1000) := '23|12.1| 450|30|9|78|82.5|92.1|120|185|52|11';
  3     l_item VARCHAR2(100);
  4  BEGIN
  5     FOR i IN 1 .. length(p_tsv) - length(REPLACE(p_tsv, '|', '')) + 1 LOOP
  6        l_item := regexp_substr(p_tsv, '[^|]+', 1, i);
  7        dbms_output.put_line(l_item);
  8     END LOOP;
  9  END;
 10  /

23
12.1
450
30
9
78
82.5
92.1
120
185
52
11

PL/SQL procedure successfully completed

更新

您仅有12列，我将直接编写查询而没有循环，它比动态SQL更高效，更易于维护(更不用说更容易编写了):

Update

You only have 12 columns, I would write the query direcly without a loop, it will be more performant and easier to maintain than dynamic SQL (not to mention infinitely easier to write):

INSERT INTO your_table
   (ID, month1, month2, month3...)
   SELECT :p_id, 
          regexp_substr(:p_tsv, '[^|]+', 1, 1) c1, 
          regexp_substr(:p_tsv, '[^|]+', 1, 2) c2,
          regexp_substr(:p_tsv, '[^|]+', 1, 3) c3
          ...
     FROM dual;

这篇关于将管道分隔的字符串解析为列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！