如何在Oracle SQL中切割URL字符串并提取参数键值对？

Hey there! Let's figure out how to solve this Oracle SQL problem where you need to extract and format URL query parameters exactly like you specified.

Your goal is to take a URL from a column, pull out the query string parameters, rename mstrContentType to ContentType, and output them as space-separated key-value pairs. Here's a complete solution that does just that:

SELECT
    LISTAGG(
        CASE 
            WHEN param_key = 'mstrContentType' THEN 'ContentType=' || cleaned_value
            ELSE param_key || '=' || cleaned_value
        END,
        ' '
    ) WITHIN GROUP (ORDER BY param_pos) AS parsed_parameters
FROM (
    SELECT
        -- Split each parameter from the query string
        REGEXP_SUBSTR(query_str, '[^&]+', 1, LEVEL) AS raw_param,
        LEVEL AS param_pos,
        -- Extract the parameter key (before the '=')
        REGEXP_SUBSTR(REGEXP_SUBSTR(query_str, '[^&]+', 1, LEVEL), '[^=]+', 1, 1) AS param_key,
        -- Extract the parameter value, replace '+' with spaces, and trim extra whitespace
        TRIM(REPLACE(
            REGEXP_SUBSTR(REGEXP_SUBSTR(query_str, '[^&]+', 1, LEVEL), '[^=]+', 1, 2),
            '+',
            ' '
        )) AS cleaned_value
    FROM (
        SELECT
            -- Grab everything after the '?' in the URL, trim leading/trailing spaces
            TRIM(SUBSTR(url_column, INSTR(url_column, '?') + 1)) AS query_str
        FROM your_table
        -- Filter rows that actually have a query string
        WHERE url_column LIKE '%?%'
    )
    -- Generate a row for each parameter using hierarchical query
    CONNECT BY REGEXP_SUBSTR(query_str, '[^&]+', 1, LEVEL) IS NOT NULL
    -- Prevent duplicate rows when processing multiple URLs
    GROUP BY query_str, LEVEL
)
GROUP BY query_str;

Let's break down what this does: #

1. Inner-most subquery: Extracts the query string (everything after the ?) from your URL column, and trims any extra whitespace that might be hanging around (like the space after & in your example URL).
2. Middle subquery: Uses a hierarchical CONNECT BY clause to split the query string into individual parameters (split on &). Then it splits each parameter into a key and value, and cleans up the value by replacing + characters with spaces (since + represents spaces in URL encoding) and trimming any leftover whitespace.
3. Outer query: Uses LISTAGG to concatenate all the cleaned key-value pairs into a single string separated by spaces. The CASE statement handles renaming mstrContentType to ContentType as you requested.

Testing with your example URL: #

If you input your sample URL:

http://example.com/wps/myportal/example/viewer?mstrContentType=Report& mstrId=15F4AC9C4453E5F75456438F732F7B8C&contentName=7.++CTI+Session+Order+Counts&proj=System+Xr+-+DMDR/CW&workspaceName=&woId=&vendorId=Microstrategy

This query will output:

ContentType=Report mstrId=15F4AC9C4453E5F75456438F732F7B8C contentName=7. CTI Session Order Counts proj=System Xr - DMDR/CW workspaceName= woId= vendorId=Microstrategy

Just replace your_table and url_column with your actual table and column names, and adjust any filters if needed!

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