如何用Oracle正则表达式解析带^分隔的特定格式字符串？

Got it, let's break down how to split your 112^3^1^1^ formatted string into the four required fields (order_no, line_no, release_no, receipt_no) using Oracle's regular expression functions. Here are a few reliable approaches:

Approach 1: Using REGEXP_SUBSTR with Capture Groups #

This is the most straightforward method—we'll target each segment by matching non-caret characters followed by a caret, then extract the captured value (the part before the caret).

SELECT
  -- Extract order_no: first segment before the first ^
  REGEXP_SUBSTR('112^3^1^1^', '([^^]+)\^', 1, 1, NULL, 1) AS order_no,
  -- Extract line_no: second segment between first and second ^
  REGEXP_SUBSTR('112^3^1^1^', '([^^]+)\^', 1, 2, NULL, 1) AS line_no,
  -- Extract release_no: third segment between second and third ^
  REGEXP_SUBSTR('112^3^1^1^', '([^^]+)\^', 1, 3, NULL, 1) AS release_no,
  -- Extract receipt_no: fourth segment (handles trailing ^)
  REGEXP_SUBSTR('112^3^1^1^', '([^^]+)\^?', 1, 4, NULL, 1) AS receipt_no
FROM DUAL;

Regex Breakdown: #

• ([^^]+): Capture group matching one or more characters that are not carets (this is our target field value).
• \^: Matches the trailing caret that separates segments.
• \^?: For the last field, makes the trailing caret optional—works even if your string ends without it (e.g., 112^3^1^1).
• The final 1 in REGEXP_SUBSTR tells Oracle to return the first capture group, not the entire matched string.

Approach 2: Using REGEXP_REPLACE to Strip Unwanted Text #

If you prefer replacing the entire string to isolate the desired segment, this method works well too:

SELECT
  -- Keep only the first segment, strip everything after
  REGEXP_REPLACE('112^3^1^1^', '^([^^]+)\^.*$', '\1') AS order_no,
  -- Keep only the second segment, strip before and after
  REGEXP_REPLACE('112^3^1^1^', '^[^^]+\^([^^]+)\^.*$', '\1') AS line_no,
  -- Keep only the third segment
  REGEXP_REPLACE('112^3^1^1^', '^[^^]+\^[^^]+\^([^^]+)\^.*$', '\1') AS release_no,
  -- Keep only the fourth segment, handle optional trailing ^
  REGEXP_REPLACE('112^3^1^1^', '^[^^]+\^[^^]+\^[^^]+\^([^^]+)\^?$', '\1') AS receipt_no
FROM DUAL;

Regex Breakdown: #

• ^[^^]+\^: Matches everything from the start up to (and including) the nth caret, depending on the field.
• ([^^]+): The capture group for our target field.
• \^?.*$: Matches an optional trailing caret and everything after it, which we replace with just the captured group (\1).

Using with Table Columns #

If you're working with data in a table instead of a hardcoded string, just replace the literal '112^3^1^1^' with your column name. For example:

SELECT
  REGEXP_SUBSTR(your_column_name, '([^^]+)\^', 1, 1, NULL, 1) AS order_no,
  REGEXP_SUBSTR(your_column_name, '([^^]+)\^', 1, 2, NULL, 1) AS line_no,
  REGEXP_SUBSTR(your_column_name, '([^^]+)\^', 1, 3, NULL, 1) AS release_no,
  REGEXP_SUBSTR(your_column_name, '([^^]+)\^?', 1, 4, NULL, 1) AS receipt_no
FROM your_table;

All these methods will correctly extract the four fields from your formatted string. Test them out with your actual data to confirm!

内容的提问来源于stack exchange，提问作者Uğur Sinan Sağıroğlu