sql - 如何通过字符串获取指定的内容

问题描述:

我的 oracle 查询 SQL 结果有一列,该列 value 是一个字符串描述,如下所示。如何获得我想要的特殊子字符串,比如我只想要飞行员的名字。

{"flightRiskValue":{"airportDifficulty":0.00,"airportExp":0.00,"airportExp_captain":0.00,"airportTechNos":0.20,"airportTechNos_captain":0.40,"flightCrewNum":0.00,"flightExp":0.00,"flightExp_captain":0.00,"groupExp":0.70,"ilsWorkStatus":0.00,"pilotCrewLimit":0.00,"pilotCrewLimit_captain":0.00,"pilotDutyLevel":0.05,"pilotDutyLevel_captain":0.05,"pilotFindAnyFemale":0.00,"pilotScheduleTechLevel":0.00,"pilotScheduleTechLevel_captain":0.00,"pilotWorkTimeMonth":0.00,"pilotWorkTimeMonth_captain":0.00,"pilotWorkTimeWeek":0.15,"pilotWorkTimeWeek_captain":0.10,"pilotsIsDeptTeam":0.00,"rain":0.00,"rainAndSnow":0.00,"seeing":0.00,"snow":0.00,"wind":0.00},"pilotDTOList":["副驾驶:5-2|杜佳佳|F3;","副驾驶:5-1|凯尔|F4;","第二机长/巡航机长:5-1|贺云|A1;","机长:5-2|张磊3|A1;"],"riskValue":1.95,"version":"1"}

预期:如何获取特殊子字符串,例如我想要的子字符串:

|杜佳佳|,|凯尔|,|贺云|,|张三|

我希望一列根据此列成为更多列

first pilot name second pilot name ....
杜佳佳 凯尔 贺云...

回答1

您可以将 JSON 数组中的每个项目直接提取到不同的列中(无需 PIVOT),然后找到子字符串(无需使用 [slow] 正则表达式):

SELECT SUBSTR(
         pilot1,
         INSTR(pilot1, '|', 1, 1) + 1,
         INSTR(pilot1, '|', 1, 2) - INSTR(pilot1, '|', 1, 1) - 1
       ) AS first_pilot_name,
       SUBSTR(
         pilot2,
         INSTR(pilot2, '|', 1, 1) + 1,
         INSTR(pilot2, '|', 1, 2) - INSTR(pilot2, '|', 1, 1) - 1
       ) AS second_pilot_name,
       SUBSTR(
         pilot3,
         INSTR(pilot3, '|', 1, 1) + 1,
         INSTR(pilot3, '|', 1, 2) - INSTR(pilot3, '|', 1, 1) - 1
       ) AS third_pilot_name,
       SUBSTR(
         pilot4,
         INSTR(pilot4, '|', 1, 1) + 1,
         INSTR(pilot4, '|', 1, 2) - INSTR(pilot4, '|', 1, 1) - 1
       ) AS fourth_pilot_name
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.value,
         '$'
         COLUMNS (
           pilot1 NVARCHAR2(200) PATH '$.pilotDTOList[0]',
           pilot2 NVARCHAR2(200) PATH '$.pilotDTOList[1]',
           pilot3 NVARCHAR2(200) PATH '$.pilotDTOList[2]',
           pilot4 NVARCHAR2(200) PATH '$.pilotDTOList[3]'
         )
       ) j

其中,对于样本数据,输出:

FIRST_PILOT_NAME SECOND_PILOT_NAME THIRD_PILOT_NAME FOURTH_PILOT_NAME
杜佳佳 凯尔 贺云 张磊3

db<>fiddlle https://dbfiddle.uk/?rdbms=oracle_21&fiddle=4e6c3f96a174b6bc30cfaa627ded2155

相似文章