SQL提取字符串中固定与可变利率：位置无关实现需求问询

Got it, let's fix this problem. Your current SQL works when the rates are in a fixed order, but it fails as soon as the fixed rate moves to a different spot—since it's tied to the 4th position. Instead, we need to classify each rate based on whether it's followed by RateLowerBound, not its position in the string.

Approach Breakdown #

• Extract all rate-related entries: First, split the input string into individual chunks that contain either a rate + RateLowerBound or just a standalone rate.
• Classify each rate: For each chunk, check if it includes RateLowerBound—if yes, it's a variable rate; if not, it's a fixed rate.
• Rank and pivot the results: Finally, number the rates within their categories and pivot them into columns to match your desired output format.

Working SQL Code (Oracle Example) #

WITH rate_entries AS (
    -- Extract each rate-related chunk (either [rate] RateLowerBound(...) or [rate])
    SELECT 
        REGEXP_SUBSTR(Col1, '\[.+?\](?:\s+RateLowerBound\(.*?\))?', 1, LEVEL) AS entry,
        LEVEL AS entry_order
    FROM (
        -- Replace this with your actual table/column
        SELECT 'Basis(DataKeyBasisAmount(basisProperty=INPUT_EPSO_PAYMENT_AMOUNT)) [+0.05] RateLowerBound(value=2, inclusive=false) | [+0.07] RateLowerBound(value=5, inclusive=false) | [+0.10] RateLowerBound(value=20, inclusive=false) | [+0.35]' AS Col1
    )
    CONNECT BY REGEXP_SUBSTR(Col1, '\[.+?\](?:\s+RateLowerBound\(.*?\))?', 1, LEVEL) IS NOT NULL
),
classified_rates AS (
    -- Extract the raw rate value and classify it
    SELECT
        entry_order,
        REPLACE(REGEXP_SUBSTR(entry, '\[(.*?)\]', 1, 1), '[', '') AS rate_value,
        CASE 
            WHEN INSTR(entry, 'RateLowerBound') > 0 THEN 'Var_Rate' 
            ELSE 'Fixed_Rate' 
        END AS rate_type
    FROM rate_entries
),
ranked_rates AS (
    -- Number rates within each category based on their original order
    SELECT
        rate_value,
        rate_type,
        ROW_NUMBER() OVER (PARTITION BY rate_type ORDER BY entry_order) AS rank_in_type
    FROM classified_rates
)
-- Pivot the results into your desired column format
SELECT
    MAX(CASE WHEN rate_type = 'Var_Rate' AND rank_in_type = 1 THEN rate_value END) AS Var_Rate_1,
    MAX(CASE WHEN rate_type = 'Var_Rate' AND rank_in_type = 2 THEN rate_value END) AS Var_Rate_2,
    MAX(CASE WHEN rate_type = 'Var_Rate' AND rank_in_type = 3 THEN rate_value END) AS Var_Rate_3,
    MAX(CASE WHEN rate_type = 'Fixed_Rate' AND rank_in_type = 1 THEN rate_value END) AS Fixed_Rate_1
FROM ranked_rates;

How It Handles Variable Order #

If your input string changes to something like:

[+0.35] | [+0.05] RateLowerBound(value=2, inclusive=false) | [+0.07] RateLowerBound(value=5, inclusive=false)

The code will correctly:

• Mark +0.35 as Fixed_Rate_1
• Mark +0.05 as Var_Rate_1
• Mark +0.07 as Var_Rate_2

Key Improvements Over Your Original Code #

• Context-based classification: Uses the presence of RateLowerBound to categorize rates, not their position.
• Scalable: Automatically handles any number of variable/fixed rates (just add more MAX(CASE...) lines if you expect more than 3 variable rates).
• Order-preserving: Keeps the original order of rates within each category.

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