Snowflake Auto URL Parsing SQL

url.sql

-- Drop the existing procedure if it exists
DROP PROCEDURE IF EXISTS SANDBOX_PRODUCT_ENGINEERING.CONTENT_OPTIMIZATION.insert_update_url_entry;

-- Create the new stored procedure
CREATE OR REPLACE PROCEDURE SANDBOX_PRODUCT_ENGINEERING.CONTENT_OPTIMIZATION.insert_update_url_entry(
    p_id FLOAT,
    p_href TEXT,
    p_name VARCHAR)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    var v_regex_pattern = '^([a-zA-Z][a-zA-Z0-9+\\-.]*:)//(([^:/?#]+)(:([^:/?#]*))?@)?([^:/?#]+)(:(\\d+))?(\/[^?#]*)?(\\?[^#]*)?(#.*)?$';
    var v_matches = P_HREF.match(v_regex_pattern);

    if (v_matches) {
        var protocol = v_matches[1];
        var username = v_matches[3] ? v_matches[3] : '';
        var password = v_matches[5] ? v_matches[5] : '';
        var host = v_matches[6];
        var port = v_matches[8] ? v_matches[8] : '';
        var pathname = v_matches[9] ? v_matches[9] : '';
        var search = v_matches[10] ? v_matches[10] : '';
        var hash = v_matches[11] ? v_matches[11] : '';

        var sqlText;
        var binds = [P_HREF, P_NAME, protocol, username, password, host, port, pathname, search, hash];

        if (P_ID !== null && !isNaN(P_ID)) {
            sqlText = `UPDATE url_entries SET href = ?, name = ?, protocol = ?, username = ?, password = ?, host = ?, port = ?, pathname = ?, search = ?, hash = ?, updated_at = CURRENT_TIMESTAMP() WHERE id = ?`;
            binds.push(P_ID);
        } else {
            sqlText = `INSERT INTO url_entries (href, name, protocol, username, password, host, port, pathname, search, hash) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
        }

        var stmt = snowflake.createStatement({ sqlText: sqlText, binds: binds });
        var result = stmt.execute();
        return 'Success';
    } else {
        return 'Invalid URL format';
    }
$$;

CALL SANDBOX_PRODUCT_ENGINEERING.CONTENT_OPTIMIZATION.insert_update_url_entry(NULL, 'https://user:pass@www.example.com:1234/path/to/resource?var1=val1&var1=val1a&var2=value%202#hashing', 'Example Site');