Azure Synapse Analytics SQL Database：规避SELECT限制实现无表依赖的分隔列表匹配判断标量函数

Great question—since we can’t use SELECT or table-valued functions like STRING_SPLIT inside a scalar UDF in Azure Synapse, we can lean purely on string manipulation logic to check for overlapping items while avoiding that restriction. Here’s an efficient implementation that exits early as soon as a match is found:

CREATE FUNCTION util.get_lsts_have_mtch (
 @p_lst_1 VARCHAR(8000),
 @p_lst_2 VARCHAR(8000),
 @p_dlmtr CHAR(1)
)
RETURNS BIT
/***********************************************************************************************************
Description: This function returns 1 if two delimited lists have an item that exists in both lists.
--Example run:
SELECT util.get_lsts_have_mtch('AB|CD|EF|GH|IJ','UV|WX|CD|IJ|YZ','|') -- returns 1, there's a match
SELECT util.get_lsts_have_mtch('AB|CD|EF|GH|IJ','ST|UV|WX|YZ','|') -- returns 0, there's no match
**********************************************************************************************************/
AS
BEGIN
    -- Handle edge cases first: empty lists can't have matches
    IF @p_lst_1 = '' OR @p_lst_2 = ''
        RETURN 0;

    -- Wrap both lists with delimiters to avoid partial matches (e.g., 'AB' vs 'ABC')
    DECLARE @v_wrapped_lst1 VARCHAR(8002) = @p_dlmtr + @p_lst_1 + @p_dlmtr;
    DECLARE @v_wrapped_lst2 VARCHAR(8002) = @p_dlmtr + @p_lst_2 + @p_dlmtr;
    DECLARE @v_current_item VARCHAR(8000);
    DECLARE @v_dlmtr_pos INT;

    -- Loop through each item in the first list
    WHILE LEN(@v_wrapped_lst1) > 2 -- More than just the wrapping delimiters left
    BEGIN
        -- Find the first delimiter position to extract the next item
        SET @v_dlmtr_pos = CHARINDEX(@p_dlmtr, @v_wrapped_lst1, 2);
        SET @v_current_item = SUBSTRING(@v_wrapped_lst1, 2, @v_dlmtr_pos - 2);

        -- Check if this item exists in the wrapped second list (exact match)
        IF CHARINDEX(@p_dlmtr + @v_current_item + @p_dlmtr, @v_wrapped_lst2) > 0
            RETURN 1;

        -- Remove the processed item from the wrapped list to move to the next one
        SET @v_wrapped_lst1 = SUBSTRING(@v_wrapped_lst1, @v_dlmtr_pos, LEN(@v_wrapped_lst1) - @v_dlmtr_pos + 1);
    END;

    -- No matches found after checking all items
    RETURN 0;
END;

Key Details: #

• Edge Case Handling: Immediately returns 0 if either list is empty, saving unnecessary processing.
• Partial Match Prevention: Wrapping lists with the delimiter (e.g., |AB|CD| instead of AB|CD) ensures we check for exact full-item matches, not accidental substring hits.
• Early Exit: As soon as a matching item is found, the function returns 1 right away—no need to process the rest of the list, which keeps this efficient especially when matches appear early in the list.
• No Table Operations: Entirely uses string functions like CHARINDEX, SUBSTRING, and LEN to stay within scalar UDF constraints.

This implementation works smoothly within Azure Synapse’s scalar UDF rules and maintains solid performance for the VARCHAR(8000) input size.

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