SQL Oracle如何将文本拆分为行并保留分隔符但不保留重复项

qc6wkl3g 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(97)

我有一个列值，例如。'IC123456IC789123'。该值是来自不同系统的聚合，因此可以是数字或字母/数字的形式，没有固定的格式，例如921253 X4525186 IC 543534
在数字可以用于另一个联接之前，需要清理数据。这是一个一致的模式与一个特定的系统，似乎结合了两个数字，例如。带有IC前缀的示例
我需要为该列创建两行，但仅限于这种情况。其他数据应该传递，并且它们应该是不同的值（但在某些情况下，根据示例，它们在列中重复）

results
'IC123456'
'IC789123'

但如果是重复行，则只应创建一行

'IC123456IC123456'
'IC123456'
'IC123456'
results 
'IC123456'

我试过REGEXP_SUBSTR('account','number','[^IC]',1,2) AS "PARTS"
但只给我没有“IC”的数字（我需要保留）。另外，在一个新的列上给出它们，但是我想在“number column”中保留，它还应该返回不包含“IC”的每一个其他数字。

oracle

来源：https://stackoverflow.com/questions/77127624/sql-oracle-how-to-split-text-into-rows-and-retain-the-deliminator-but-not-duplic

2条答案

按热度按时间

gr8qqesn1#

您可以使用Connect by子句通过IC关键字将字符串拆分为2行，然后使用distinct关键字获取唯一值-

select distinct 'IC' || trim(regexp_substr(value,'[^IC]+', 1, level) ) value
  from your_table
   connect by regexp_substr(value, '[^IC]+', 1, level) is not null;

Demo.

赞(0）回复(0）举报 2023-10-16

xyhw6mcr2#

假设你的值都是8个字符长（IC然后是6个数字），那么你可以使用简单的字符串函数（比正则表达式快得多）和递归子查询因子子句：

WITH rsqfc (id, value, idx) AS (
  SELECT id, value, 1 FROM table_name
UNION ALL
  SELECT id, value, idx + 1 FROM rsqfc WHERE idx + 1 <= LENGTH(value)/8
)
SELECT DISTINCT
       id,
       SUBSTR(value, 8*idx - 7, 8) AS value
FROM   rsqfc

如果你有可变长度的术语（都以IC开头），那么你可以用途：

WITH rsqfc (id, value, spos, epos) AS (
  SELECT id,
         value,
         INSTR(value, 'IC', 1, 1),
         INSTR(value, 'IC', 1, 2)
  FROM   table_name
UNION ALL
  SELECT id,
         value,
         epos,
         INSTR(value, 'IC', epos + 2, 1)
  FROM   rsqfc
  WHERE  epos > 0
)
SELECT DISTINCT
       id,
       CASE epos
       WHEN 0
       THEN SUBSTR(value, spos)
       ELSE SUBSTR(value, spos, epos - spos)
       END AS value
FROM   rsqfc

如果你有采用字母数字形式的可变长度项，那么你可以使用正则表达式：

WITH rsqfc (id, value, term, epos) AS (
  SELECT id,
         value,
         REGEXP_SUBSTR(value, '[A-Z]+\d+', 1, 1),
         REGEXP_INSTR(value, '[A-Z]+\d+', 1, 1, 1)
  FROM   table_name
UNION ALL
  SELECT id,
         value,
         REGEXP_SUBSTR(value, '[A-Z]+\d+', epos, 1),
         REGEXP_INSTR(value, '[A-Z]+\d+', epos, 1, 1)
  FROM   rsqfc
  WHERE  epos < LENGTH(value)
)
SELECT DISTINCT
       id,
       term AS value
FROM   rsqfc

或者，使用简单的字符串函数（这可能比正则表达式更容易输入，但可能更快）：

WITH patterns (id, value, pattern) AS (
  SELECT id,
         value,
         TRANSLATE(
           value,
           'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
           'XXXXXXXXXXXXXXXXXXXXXXXXXX0000000000'
         )
  FROM   table_name
),
rsqfc (id, value, pattern, spos, epos) AS (
  SELECT id,
         value,
         pattern,
         INSTR(pattern, 'X'),
         INSTR(pattern, 'X', INSTR(pattern, '0'))
  FROM   patterns
UNION ALL
  SELECT id,
         value,
         pattern,
         epos,
         INSTR(pattern, 'X', INSTR(pattern, '0', epos))
  FROM   rsqfc
  WHERE  epos > 0
)
SELECT DISTINCT
       id,
       CASE epos
       WHEN 0
       THEN SUBSTR(value, spos)
       ELSE SUBSTR(value, spos, epos - spos)
       END AS value
FROM   rsqfc

其中，对于样本数据：

CREATE TABLE table_name (id, value) AS
SELECT 1, 'IC123456IC789123' FROM DUAL UNION ALL
SELECT 2, 'IC123456IC123456' FROM DUAL;

所有输出：
| ID|值|
| --|--|
| 1 |IC123456|
| 2 |IC123456|
| 1 |IC789123|

注意：id = 2不重复。*

fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

SQL Oracle如何将文本拆分为行并保留分隔符但不保留重复项

2条答案

相关问题

热门标签

最新问答