如何在PL/SQL中从含分隔符的字符串提取可变长度Origin编号？

Got it, let's figure out how to pull that Origin number (like 8385 in your example) from the string stored in your slv varchar variable. Since the number's length isn't fixed, hardcoding a substring length won't work—we need dynamic ways to target the value between ORIGIN=L= and the next key in the string.

Here are two reliable approaches, depending on whether your database supports regular expressions:

1. Regular Expression Method (Simplest & Most Flexible) #

If your database (like Oracle, PostgreSQL, MySQL) supports regex functions, this is the way to go. We can use a regex pattern to match the ORIGIN=L= prefix and capture the following sequence of digits.

Example for Oracle SQL: #

SELECT REGEXP_SUBSTR(slv, 'ORIGIN=L=(\d+)', 1, 1, NULL, 1) AS origin_number
FROM your_table;

Breakdown: #

• ORIGIN=L= : Exact match for the prefix that precedes our target number.
• (\d+) : Captures one or more digits (this is our Origin number). The parentheses mark this as a "capture group".
• The final 1 parameter tells the function to return the content of the first capture group instead of the entire matched string.

This works even if Origin is the last entry in the string—\d+ will grab all digits until the end.

2. String Function Method (No Regex Required) #

If regex isn't an option, we can combine INSTR and SUBSTR to dynamically find the start and end positions of the Origin number. The idea is to:

1. Find the end of the ORIGIN=L= prefix.
2. Locate the start of the next key (which starts with an uppercase letter, like WORKGROUP or MAINORIGIN).
3. Substring from the end of the prefix to just before the next key.

Example for Oracle SQL: #

SELECT CASE
    WHEN INSTR(slv, 'ORIGIN=L=') = 0 THEN NULL -- Handle case where ORIGIN doesn't exist
    ELSE SUBSTR(
        slv,
        -- Start position: right after ORIGIN=L=
        INSTR(slv, 'ORIGIN=L=') + LENGTH('ORIGIN=L='),
        -- Calculate length: distance from start to next uppercase letter (or end of string)
        CASE
            WHEN INSTR(slv, UPPER(SUBSTR(slv, INSTR(slv, 'ORIGIN=L=') + LENGTH('ORIGIN=L='), 1)), INSTR(slv, 'ORIGIN=L=') + LENGTH('ORIGIN=L=')) = 0 THEN
                LENGTH(slv) - (INSTR(slv, 'ORIGIN=L=') + LENGTH('ORIGIN=L=')) + 1
            ELSE
                INSTR(slv, UPPER(SUBSTR(slv, INSTR(slv, 'ORIGIN=L=') + LENGTH('ORIGIN=L='), 1)), INSTR(slv, 'ORIGIN=L=') + LENGTH('ORIGIN=L=')) 
                - (INSTR(slv, 'ORIGIN=L=') + LENGTH('ORIGIN=L='))
        END
    )
END AS origin_number
FROM your_table;

Breakdown: #

• We first check if ORIGIN=L= exists in the string to avoid errors.
• For the end position: We look for the first uppercase letter starting right after ORIGIN=L=. If there's no such letter (meaning Origin is the last entry), we use the end of the string.

Both methods will correctly extract the Origin number regardless of its length—whether it's 4 digits like your example or longer/shorter.

内容的提问来源于stack exchange，提问作者Vaibhav Oberoi