更新时间:2022-11-15 08:05:10
如果您希望所有这些都作为 row 中的单个字符串,则无需使用正则表达式,您可以使用标准 REPLACE()
:
If you want all of these as a single string in a row them there's no need to use regular expressions you can use a standard REPLACE()
:
SQL> select replace('2711393|2711441|1234567', '|', ', ') from dual;
REPLACE('2711393|2711441|
-------------------------
2711393, 2711441, 1234567
如果要将所有这些都放在一个列中,则需要使用 CONNECT BY
,因为我演示了
If you want all of these in a single column then you need to use CONNECT BY
as I demonstrate here. Please note that this is highly inefficient.
SQL> select regexp_substr('2711393|2711441|1234567', '[^|]+', 1, level)
2 from dual
3 connect by regexp_substr('2711393|2711441|1234567'
4 , '[^|]+', 1, level) is not null;
REGEXP_SUBSTR('2711393|2711441|1234567','[^|]+',1,LEVEL)
--------------------------------------------------------------------------
2711393
2711441
1234567
SQL>
If you want these in different columns you need to use PIVOT
and you'll need to know how many you have. I'm assuming 3.
SQL> select *
2 from (
3 select regexp_substr('2711393|2711441|1234567', '[^|]+', 1, level) as a
4 , level as lvl
5 from dual
6 connect by regexp_substr('2711393|2711441|1234567'
7 , '[^|]+', 1, level) is not null
8 )
9 pivot ( max(a)
10 for lvl in (1,2,3)
11 )
12 ;
1 2 3
---------- ---------- ----------
2711393 2711441 1234567
SQL>
如您所见,这些都是完全可怕的,除了第一个,效率很低.您应该正确规范化数据库,以确保不必这样做.
As you can see these are all completely horrible and, save the first, highly inefficient. You should normalise your database correctly to ensure you don't have to do this.