将文本从分隔字符串提取到另一个分隔字符串TSQL

DECLARE @input_string VARCHAR(100) = 'apple,banana,orange';
DECLARE @delimiter_source CHAR(1) = ',';
DECLARE @delimiter_target CHAR(1) = '|';

-- 创建临时表
CREATE TABLE #temp_table (value VARCHAR(100));

-- 拆分原始文本并插入临时表
DECLARE @start_pos INT, @end_pos INT;
SET @start_pos = 1;
SET @end_pos = CHARINDEX(@delimiter_source, @input_string);

WHILE @end_pos > 0
BEGIN
    INSERT INTO #temp_table (value)
    VALUES (SUBSTRING(@input_string, @start_pos, @end_pos - @start_pos));

    SET @start_pos = @end_pos + 1;
    SET @end_pos = CHARINDEX(@delimiter_source, @input_string, @start_pos);
END

-- 处理最后一个子字符串
INSERT INTO #temp_table (value)
VALUES (SUBSTRING(@input_string, @start_pos, LEN(@input_string) - @start_pos + 1));

-- 生成目标分隔字符串
DECLARE @output_string VARCHAR(100);
SET @output_string = '';

SELECT @output_string = @output_string + value + @delimiter_target
FROM #temp_table;

-- 移除最后一个分隔符
SET @output_string = LEFT(@output_string, LEN(@output_string) - 1);

-- 输出结果
SELECT @output_string AS output_string;

-- 删除临时表
DROP TABLE #temp_table;