如何將以特定str開頭的字串的特定部分決議為Oracle中的第一個空格

我有這樣的資料集：

SELECT 
    1 as text_id,
    'The first is A.ACCOUNT_ID and the second one is B.IDENTITY_NO and third one is plate_number .' as full_text
FROM DUAL
UNION
SELECT 
    2,
    'The first is ARC.PREV_RECORD and the second one is ARC.NEXT_RECORD .'
FROM DUAL

我應該決議所有以“是”開頭的短語；以每行的第一個空格字符結尾。

所以我想要達到的結果full_text是：

它可能少于或多于 3 個短語，因此結果的行數可能會發生變化。

我嘗試先反轉文本并找到“ si ”和空格之間的部分但無法成功

reverse(regexp_substr(reverse(full_text), ' si ([^_]*) ',1, 1))

uj5u.com熱心網友回復：

根據您發布的內容，看看這是否有幫助。

樣本資料

SQL> with test as
  2  (SELECT
  3      1 as text_id,
  4      'The first is A.ACCOUNT_ID and the second one is B.IDENTITY_NO and third one is plate_number .' as full_text
  5  FROM DUAL
  6  UNION
  7  SELECT
  8      2,
  9      'The first is ARC.PREV_RECORD and the second one is ARC.NEXT_RECORD .'
 10  FROM DUAL
 11  )

查詢自身

 12  select text_id,
 13    ltrim(regexp_substr(full_text, 'is \w .\w ', 1, column_value), 'is ') parsed_part
 14  from test cross join
 15    table(cast(multiset(select level from dual
 16                        connect by level <= regexp_count(full_text, ' is ')
 17                       ) as sys.odcinumberlist))
 18  order by text_id, column_value;

   TEXT_ID PARSED_PART
---------- --------------------
         1 A.ACCOUNT_ID
         1 B.IDENTITY_NO
         1 plate_number
         2 ARC.PREV_RECORD
         2 ARC.NEXT_RECORD

SQL>

• regexp_substr搜索is后跟兩個用點分隔的單詞的字串
• ltrim 洗掉前導 is

uj5u.com熱心網友回復：

盡管 Littlefoot 擊敗了我，但這是我的方法：

WITH tbl(text_id, full_text) AS (
  SELECT 1, 'The first is A.ACCOUNT_ID and the second one is B.IDENTITY_NO and third one is plate_number .' FROM DUAL UNION ALL
  SELECT 2, 'The first is ARC.PREV_RECORD and the second one is ARC.NEXT_RECORD .' FROM DUAL
)
SELECT text_id,  
       REGEXP_SUBSTR(full_text, ' is (.*?)( |$)', 1, LEVEL, NULL, 1) parsed_part
FROM tbl
CONNECT BY LEVEL <= REGEXP_COUNT(full_text, ' is .*?( |$)')
  AND PRIOR text_id = text_id
  AND PRIOR SYS_GUID() IS NOT NULL; 


   TEXT_ID PARSED_PART         
---------- --------------------
         1 A.ACCOUNT_ID        
         1 B.IDENTITY_NO       
         1 plate_number        
         2 ARC.PREV_RECORD     
         2 ARC.NEXT_RECORD     

5 rows selected.

uj5u.com熱心網友回復：

通過橫向連接展開虛線單詞connect by。

WITH DATA AS (
SELECT 1 as textid, 'The first is A.ACCOUNT_ID and the second one is B.IDENTITY_NO and third one is plate_number .' as full_text FROM DUAL
UNION ALL SELECT 2, 'The first is ARC.PREV_RECORD and the second one is ARC.NEXT_RECORD .' FROM DUAL
) 
SELECT t.textid, w.word
FROM DATA t
CROSS JOIN LATERAL (
  SELECT level AS lvl, REGEXP_SUBSTR(full_text, ' is ([A-Z._] )',1, LEVEL, 'i', 1) AS word 
  FROM DUAL
  CONNECT BY LEVEL <= REGEXP_COUNT(full_text, ' is ([A-Z._] )', 1, 'i')
) w
ORDER BY t.textid;

文本ID | 單詞           
-----: | :--------------
     1 | A.ACCOUNT_ID   
     1 | B.IDENTITY_NO  
     1 | 車牌號碼   
     2 | ARC.PREV_RECORD
     2 | ARC.NEXT_RECORD

db<>在這里擺弄

uj5u.com熱心網友回復：

您不需要使用（慢）正則運算式，并且可以在遞回子查詢中使用簡單的字串函式：

WITH bounds (text_id, full_text, start_pos, end_pos) AS (
  SELECT text_id,
         full_text,
         INSTR(full_text, ' is ', 1),
         INSTR(full_text, ' ', INSTR(full_text, ' is ', 1)   4 )
  FROM   table_name
  WHERE  INSTR(full_text, ' is ', 1) > 0
UNION ALL
  SELECT text_id,
         full_text,
         INSTR(full_text, ' is ', end_pos),
         INSTR(full_text, ' ', INSTR(full_text, ' is ', end_pos)   4 )
  FROM   bounds
  WHERE  start_pos > 0
  AND    end_pos > 0
)
SEARCH DEPTH FIRST BY text_id SET order_rn
SELECT text_id,
       SUBSTR(full_text, start_pos   4, end_pos - start_pos - 4) AS parsed_part
FROM   bounds
WHERE  start_pos > 0
AND    end_pos > 0;

其中，對于樣本資料：

CREATE TABLE table_name (text_id, full_text) AS
SELECT 1,
       'The first is A.ACCOUNT_ID and the second one is B.IDENTITY_NO and third one is plate_number .'
FROM   DUAL
UNION ALL
SELECT 2,
       'The first is ARC.PREV_RECORD and the second one is ARC.NEXT_RECORD .'
FROM   DUAL

輸出：

TEXT_ID	PARSED_PART
1	A.ACCOUNT_ID
1	B.IDENTITY_NO
1	車牌號碼
2	ARC.PREV_RECORD
2	ARC.NEXT_RECORD

db<>在這里擺弄

標籤：

上一篇：SQL：如何從資料字典中的鍵中提取特定值

下一篇：如何檢查值是否必須存在于另一個表中？