如何在Snowflake中实现SQL WHILE循环？求转换现有SQL函数

原SQL Server函数的核心逻辑是提取所有转义后的<li>标签内容，筛选出包含未来3个月内日期的条目，最后用筛选后的内容替换原文本中所有<li>片段。以下是两种Snowflake兼容的实现方式：

方案1：使用JavaScript UDF（贴合原WHILE循环逻辑） #

Snowflake的JavaScript UDF支持循环结构，能直接复刻原代码的逻辑：

CREATE OR REPLACE FUNCTION EZYVET.FN_PARSECOMMUNICATIONCONTENT(ORIGINALCONTENT VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$
    let content = ORIGINALCONTENT;
    let sol = '';
    let solRefined = '';
    const liStartRegex = /<li>/g;
    const liEndRegex = /<\/li>/g;

    let matchStart;
    while ((matchStart = liStartRegex.exec(content)) !== null) {
        // 找到当前<li>对应的</li>位置
        const startIdx = matchStart.index;
        const endMatch = liEndRegex.exec(content.substring(startIdx));
        if (!endMatch) break; // 无闭合标签，终止循环
        const endIdx = startIdx + endMatch.index + endMatch[0].length;
        
        const extractText = content.substring(startIdx, endIdx);
        sol += extractText;

        // 提取日期并判断是否在未来3个月内
        if (extractText.length >= 15) {
            const dateStr = extractText.slice(-15, -5); // 对应原LEFT(RIGHT(@extracttext,15),10)
            const date = new Date(dateStr);
            if (!isNaN(date.getTime())) {
                const now = new Date();
                // 计算日期差是否<=3个月
                const monthDiff = (date.getFullYear() - now.getFullYear()) * 12 + (date.getMonth() - now.getMonth());
                if (monthDiff <= 3) {
                    solRefined += extractText;
                }
            }
        }

        // 移除已处理的<li>片段
        content = content.replace(extractText, '');
        // 重置正则表达式的lastIndex，避免死循环
        liStartRegex.lastIndex = 0;
        liEndRegex.lastIndex = 0;
    }

    return ORIGINALCONTENT.replace(sol, solRefined);
$$;

方案2：使用SQL UDF（递归CTE实现遍历） #

如果偏好纯SQL实现，可以用递归CTE来提取所有<li>片段，再进行筛选和替换：

CREATE OR REPLACE FUNCTION EZYVET.FN_PARSECOMMUNICATIONCONTENT(ORIGINALCONTENT VARCHAR)
RETURNS VARCHAR
AS $$
WITH RECURSIVE li_fragments AS (
    -- 初始步骤：提取第一个<li>片段
    SELECT
        ORIGINALCONTENT AS remaining_content,
        REGEXP_SUBSTR(ORIGINALCONTENT, '&lt;li&gt;.*?&lt;\/li&gt;', 1, 1) AS extract_text,
        1 AS iteration
    WHERE REGEXP_LIKE(ORIGINALCONTENT, '&lt;li&gt;.*?&lt;\/li&gt;')
    UNION ALL
    -- 递归步骤：提取后续的<li>片段
    SELECT
        REPLACE(remaining_content, extract_text, '') AS remaining_content,
        REGEXP_SUBSTR(REPLACE(remaining_content, extract_text, ''), '&lt;li&gt;.*?&lt;\/li&gt;', 1, 1) AS extract_text,
        iteration + 1
    FROM li_fragments
    WHERE extract_text IS NOT NULL
),
-- 收集所有片段并处理筛选条件
processed_fragments AS (
    SELECT
        LISTAGG(extract_text, '') WITHIN GROUP (ORDER BY iteration) AS sol,
        LISTAGG(
            CASE
                WHEN TRY_TO_DATE(LEFT(RIGHT(extract_text, 15), 10)) IS NOT NULL
                     AND DATEDIFF(MONTH, CURRENT_DATE(), TRY_TO_DATE(LEFT(RIGHT(extract_text, 15), 10))) <= 3
                THEN extract_text
                ELSE ''
            END, ''
        ) WITHIN GROUP (ORDER BY iteration) AS sol_refined
    FROM li_fragments
    WHERE extract_text IS NOT NULL
)
SELECT
    CASE
        WHEN (SELECT sol FROM processed_fragments) IS NOT NULL
        THEN REPLACE(ORIGINALCONTENT, (SELECT sol FROM processed_fragments), (SELECT sol_refined FROM processed_fragments))
        ELSE ORIGINALCONTENT
    END
$$;

关键转换说明 #

• 数据类型：Snowflake中用VARCHAR替代SQL Server的NVARCHAR(MAX)，默认支持最大长度。
• 日期函数：用CURRENT_DATE()替代GETDATE()，TRY_TO_DATE()替代TRY_CAST(... AS DATE)，DATEDIFF(MONTH, ...)用法和SQL Server一致。
• 循环逻辑：JavaScript UDF直接用while循环复刻原逻辑；SQL UDF通过递归CTE实现遍历，避免使用WHILE循环（Snowflake SQL UDF不支持WHILE）。
• 字符串匹配：用正则表达式REGEXP_SUBSTR替代PATINDEX+SUBSTRING的组合，更简洁高效。

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