SQL中拆分带分隔符的字符串并关联表取值的技术问询

Alright, let's tackle this problem head-on. I totally get the constraint of not being able to modify the table structure in a large app—those legacy setups can be tricky! The goal is to split the hobbies field (which uses ^ as a delimiter, including a trailing ^) and join it with the hobbyValue table to map IDs to actual hobby names.

Below are solutions for the most common SQL databases:

MySQL Solution #

MySQL 8.0+ supports recursive CTEs, which is a clean way to split the string without writing a separate function. If you're on an older version, a custom table-valued function works too.

Recursive CTE Approach (No Function Needed) #

WITH RECURSIVE hobby_split AS (
    SELECT 
        user_id, -- Replace with your actual primary key column from the hobby table
        SUBSTRING_INDEX(hobbies, '^', 1) AS hobby_id,
        SUBSTRING(hobbies, LOCATE('^', hobbies) + 1) AS remaining_hobbies
    FROM hobby
    WHERE hobbies != ''
    UNION ALL
    SELECT 
        user_id,
        SUBSTRING_INDEX(remaining_hobbies, '^', 1) AS hobby_id,
        SUBSTRING(remaining_hobbies, LOCATE('^', remaining_hobbies) + 1) AS remaining_hobbies
    FROM hobby_split
    WHERE remaining_hobbies != ''
)
SELECT 
    hs.user_id,
    hv.Value AS hobby_name
FROM hobby_split hs
JOIN hobbyValue hv ON hs.hobby_id = hv.id
WHERE hs.hobby_id != ''; -- Filter out empty values from the trailing ^

Custom Table-Valued Function #

If you prefer a reusable function:

DELIMITER //
CREATE FUNCTION split_hobbies(p_hobbies VARCHAR(255))
RETURNS TABLE (hobby_id VARCHAR(50))
DETERMINISTIC
BEGIN
    RETURN (
        WITH RECURSIVE split AS (
            SELECT SUBSTRING_INDEX(p_hobbies, '^', 1) AS id, SUBSTRING(p_hobbies, LOCATE('^', p_hobbies)+1) AS rest
            WHERE p_hobbies != ''
            UNION ALL
            SELECT SUBSTRING_INDEX(rest, '^', 1) AS id, SUBSTRING(rest, LOCATE('^', rest)+1) AS rest
            FROM split
            WHERE rest != ''
        )
        SELECT id FROM split WHERE id != ''
    );
END //
DELIMITER ;

Usage:

SELECT 
    h.user_id,
    hv.Value AS hobby_name
FROM hobby h
JOIN split_hobbies(h.hobbies) hs
JOIN hobbyValue hv ON hs.hobby_id = hv.id;

SQL Server Solution #

SQL Server 2016+ has the built-in STRING_SPLIT function, which simplifies things. For older versions, a custom function is needed.

Built-in STRING_SPLIT Approach #

SELECT 
    h.user_id,
    hv.Value AS hobby_name
FROM hobby h
CROSS APPLY STRING_SPLIT(h.hobbies, '^') hs
JOIN hobbyValue hv ON hs.value = hv.id
WHERE hs.value != ''; -- Filter empty values from the trailing ^

Custom Table-Valued Function (For Older Versions) #

CREATE FUNCTION dbo.SplitHobbies(@hobbies NVARCHAR(MAX))
RETURNS @Result TABLE (HobbyId NVARCHAR(50))
AS
BEGIN
    DECLARE @Delimiter CHAR(1) = '^'
    DECLARE @StartIndex INT = 1
    DECLARE @EndIndex INT

    WHILE CHARINDEX(@Delimiter, @hobbies, @StartIndex) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @hobbies, @StartIndex)
        INSERT INTO @Result (HobbyId)
        SELECT SUBSTRING(@hobbies, @StartIndex, @EndIndex - @StartIndex)
        SET @StartIndex = @EndIndex + 1
    END

    -- Handle any remaining content (skip empty from trailing ^)
    IF @StartIndex <= LEN(@hobbies)
        INSERT INTO @Result (HobbyId)
        SELECT SUBSTRING(@hobbies, @StartIndex, LEN(@hobbies) - @StartIndex + 1)
    
    -- Remove empty entries
    DELETE FROM @Result WHERE HobbyId = ''
    RETURN
END

Usage:

SELECT 
    h.user_id,
    hv.Value AS hobby_name
FROM hobby h
CROSS APPLY dbo.SplitHobbies(h.hobbies) hs
JOIN hobbyValue hv ON hs.HobbyId = hv.id;

PostgreSQL Solution #

PostgreSQL makes this easy with STRING_TO_ARRAY and UNNEST:

Direct Query Approach #

SELECT 
    h.user_id,
    hv.Value AS hobby_name
FROM hobby h
CROSS JOIN UNNEST(STRING_TO_ARRAY(h.hobbies, '^')) AS hs(hobby_id)
JOIN hobbyValue hv ON hs.hobby_id = hv.id
WHERE hs.hobby_id != ''; -- Filter empty values from trailing ^

Custom Function (Reusable) #

CREATE OR REPLACE FUNCTION split_hobbies(p_hobbies TEXT)
RETURNS TABLE(hobby_id TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT unnest(string_to_array(p_hobbies, '^')) AS id
    WHERE unnest(string_to_array(p_hobbies, '^')) != '';
END;
$$ LANGUAGE plpgsql;

Usage:

SELECT 
    h.user_id,
    hv.Value AS hobby_name
FROM hobby h
CROSS JOIN split_hobbies(h.hobbies) hs
JOIN hobbyValue hv ON hs.hobby_id = hv.id;

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