sql - 根据字符串中的序列分配字符串 - Oracle SQL
问题描述
我在 Oracle SQL 的表格中的一个单元格中有一个文本:
ISA*00*AUTHORIZAT*00*SECURITY I*ZZ*000000060000000*ZZ*000000010000000*110705*1132*^*00501*110705001*0*T*:~
GS*HC*00000006*00000001*20110705*113253*110705001*X*005010X222A1~
ST*837*0021*005010X222~
BHT*0019*00*244579*20061015*1023*CH~
NM1*41*2*PREMIER BILLING SERVICE*****46*TGJ23~
PER*IC*JERRY*TE*3055552222*EX*231~
NM1*40*2*KEY INSURANCE COMPANY*****46*66783JJT~
HL*1**20*1~
PRV*BI*PXC*203BF0100Y~
NM1*85*2*BEN KILDARE SERVICE*****XX*9876543210~
N3*234 SEAWAY ST~
N4*MIAMI*FL*33111~
请注意,以上内容位于 1 个单元格中。我需要将其分解为多行并在其上分配一个循环。
该表如下所示:
loop message
000 ISA*00*AUTHORIZAT*00*SECURITY I*ZZ*000000060000000*ZZ*000000010000000*110705*1132*^*00501*110705001*0*T*:~
000 GS*HC*00000006*00000001*20110705*113253*110705001*X*005010X222A1~
000 ST*837*0021*005010X222~
000 BHT*0019*00*244579*20061015*1023*CH~
1000A NM1*41*2*PREMIER BILLING SERVICE*****46*TGJ23~
1000A PER*IC*JERRY*TE*3055552222*EX*231~
1000B NM1*40*2*KEY INSURANCE COMPANY*****46*66783JJT~
2000A HL*1**20*1~
2010AA PRV*BI*PXC*203BF0100Y~
2010AA NM1*85*2*BEN KILDARE SERVICE*****XX*9876543210~
2010AA N3*234 SEAWAY ST~
2010AA N4*MIAMI*FL*33111~
所以,基本上,我需要根据新行拆分字符串,然后根据序列分配适当的循环。规则是:
- 第一个 NM1 应该是 1000A 并继续分配到下一个 NM1
- 下一个 NM1 应该是 1000B 并继续分配直到下一个 PRV
- 下一个 PRV 应该是 2010AA 并继续分配到下一个 NM1
我能够使用正则表达式成功地将文本分成多行,但无法根据条件分配值。
有没有办法在 Oracle SQL 中对其进行编码?
解决方案
甲骨文设置:
CREATE TABLE test_data ( value ) AS
SELECT 'ISA*00*AUTHORIZAT*00*SECURITY I*ZZ*000000060000000*ZZ*000000010000000*110705*1132*^*00501*110705001*0*T*:~
GS*HC*00000006*00000001*20110705*113253*110705001*X*005010X222A1~
ST*837*0021*005010X222~
BHT*0019*00*244579*20061015*1023*CH~
NM1*41*2*PREMIER BILLING SERVICE*****46*TGJ23~
PER*IC*JERRY*TE*3055552222*EX*231~
NM1*40*2*KEY INSURANCE COMPANY*****46*66783JJT~
HL*1**20*1~
PRV*BI*PXC*203BF0100Y~
NM1*85*2*BEN KILDARE SERVICE*****XX*9876543210~
N3*234 SEAWAY ST~
N4*MIAMI*FL*33111~'
FROM DUAL;
查询:
WITH rsqfc ( value, line, rn, max_rn, nm1, prv, depth ) AS (
SELECT value,
REGEXP_SUBSTR( value, '.+?(' || CHR(10) || '|$)', 1, 1 ),
1,
REGEXP_COUNT( value, '.+?(' || CHR(10) || '|$)' ) - 1,
CASE SUBSTR( value, 1, 4 ) WHEN 'NM1*' THEN 1 ELSE 0 END,
CASE SUBSTR( value, 1, 4 ) WHEN 'PRV*' THEN 1 ELSE 0 END,
CASE SUBSTR( value, 1, 4 ) WHEN 'NM1*' THEN 1 WHEN 'PRV*' THEN 2 ELSE 0 END
FROM test_data
UNION ALL
SELECT value,
REGEXP_SUBSTR( value, '.+?(' || CHR(10) || '|$)', 1, rn + 1 ),
rn + 1,
max_rn,
CASE SUBSTR( REGEXP_SUBSTR( value, '.+?(' || CHR(10) || '|$)', 1, rn + 1 ), 1, 4 ) WHEN 'NM1*' THEN nm1 + 1 ELSE nm1 END,
CASE SUBSTR( REGEXP_SUBSTR( value, '.+?(' || CHR(10) || '|$)', 1, rn + 1 ), 1, 4 ) WHEN 'PRV*' THEN prv + 1 ELSE prv END,
CASE SUBSTR( REGEXP_SUBSTR( value, '.+?(' || CHR(10) || '|$)', 1, rn + 1 ), 1, 4 ) WHEN 'NM1*' THEN 1 WHEN 'PRV*' THEN 2 ELSE depth END
FROM rsqfc
WHERE rn < max_rn
)
SELECT CASE depth
WHEN 2 THEN '2010A' || CHR( 64 + prv )
WHEN 1 THEN '1000' || CHR( 64 + nm1 )
WHEN 0 THEN '000'
END AS "LOOP",
line
FROM rsqfc;
输出:
循环 | 线 :----- | :------------------------------------------------ -------------------------------------------------- ---------- 000 | ISA*00*AUTHORIZAT*00*SECURITY I*ZZ*000000060000000*ZZ*000000010000000*110705*1132*^*00501*110705001*0*T*:~<br> 000 | GS*HC*00000006*00000001*20110705*113253*110705001*X*005010X222A1~<br> 000 | ST*837*0021*005010X222~<br> 000 | BHT*0019*00*244579*20061015*1023*CH~<br> 1000A | NM1*41*2*高级计费服务*****46*TGJ23~<br> 1000A | PER*IC*JERRY*TE*3055552222*EX*231~<br> 1000B | NM1*40*2*KEY 保险公司*****46*66783JJT~<br> 1000B | HL*1**20*1~<br> 2010AA | PRV*BI*PXC*203BF0100Y~<br> 1000℃ | NM1*85*2*BEN KILDARE SERVICE*****XX*9876543210~<br> 1000℃ | N3*234 SEAWAY ST~<br>
db<>在这里摆弄
推荐阅读
- python-3.x - 如何将 AWS Kinesis 视频流的音频流输出馈送到 AWS Transcribe 服务?
- javascript - getElementsByTagName 的错误用例?
- wordpress - 从后端 woocommerce 添加时立即共享产品
- python-3.x - Python - 列表索引必须是整数或切片,而不是 str
- php - 将文件写入新创建的文件夹时访问被拒绝
- ruby-on-rails - 设计令牌身份验证使用密码重置令牌查找用户
- ios - URL 图像不适用于 Swift Playgrounds 中的 UIButton
- swift - 如何在 Cocoa 应用程序中设置自定义标签顺序
- jbpm - 无法访问 jBPM 控制台
- java - Java Spring/Apache POI/THymeleaf - 从 MSWord 到 HTML 的格式化样式