在SQL Server中如何创建临时列替代重复的字符转换操作？

不需要必须用CTE，有多种方式可以将转换后的comment列存为临时复用的列，以下是几种常用方案：

方案1：使用CTE（公共表表达式） #

CTE是最简洁的方式之一，先在CTE中定义转换后的列，后续查询直接引用即可：

WITH CommentCTE AS (
    SELECT 
        *,
        CONVERT(VARCHAR(MAX), comment) AS conv_comment
    FROM your_table
)
SELECT 
    *,
    REPLACE(
        LEFT(
            SUBSTRING(conv_comment, CHARINDEX('ABILITIES:', conv_comment) + 12, LEN(conv_comment)),
            CASE 
                WHEN CHARINDEX('.', SUBSTRING(conv_comment, CHARINDEX('ABILITIES:', conv_comment) + 12, LEN(conv_comment))) = 0
                    THEN 0
                ELSE CHARINDEX('.', SUBSTRING(conv_comment, CHARINDEX('ABILITIES:', conv_comment) + 12, LEN(conv_comment))) - 1 
            END
        ), ',', '') AS extracted_text
FROM CommentCTE;

还可以进一步在CTE中提取ABILITIES:之后的子串，减少重复计算：

WITH CommentCTE AS (
    SELECT 
        *,
        CONVERT(VARCHAR(MAX), comment) AS conv_comment,
        SUBSTRING(
            CONVERT(VARCHAR(MAX), comment),
            CHARINDEX('ABILITIES:', CONVERT(VARCHAR(MAX), comment)) + 12,
            LEN(CONVERT(VARCHAR(MAX), comment))
        ) AS after_abilities
    FROM your_table
)
SELECT 
    *,
    REPLACE(
        LEFT(after_abilities, 
             CASE WHEN CHARINDEX('.', after_abilities) = 0 THEN 0 ELSE CHARINDEX('.', after_abilities) - 1 END
        ), ',', '') AS extracted_text
FROM CommentCTE;

方案2：使用子查询 #

如果不想用CTE，子查询也能实现同样的效果，把转换后的列放在子查询中：

SELECT 
    *,
    REPLACE(
        LEFT(
            SUBSTRING(conv_comment, CHARINDEX('ABILITIES:', conv_comment) + 12, LEN(conv_comment)),
            CASE 
                WHEN CHARINDEX('.', SUBSTRING(conv_comment, CHARINDEX('ABILITIES:', conv_comment) + 12, LEN(conv_comment))) = 0
                    THEN 0
                ELSE CHARINDEX('.', SUBSTRING(conv_comment, CHARINDEX('ABILITIES:', conv_comment) + 12, LEN(conv_comment))) - 1 
            END
        ), ',', '') AS extracted_text
FROM (
    SELECT 
        *,
        CONVERT(VARCHAR(MAX), comment) AS conv_comment
    FROM your_table
) AS subquery;

方案3：使用表变量或临时表 #

如果需要在多个查询中复用转换后的列，可以用表变量或临时表存储中间结果：

表变量示例 #

DECLARE @TempComments TABLE (
    -- 需包含原表主键及其他必要列，加上转换后的列
    id INT,
    comment NVARCHAR(MAX),
    conv_comment VARCHAR(MAX)
);

INSERT INTO @TempComments
SELECT 
    id,
    comment,
    CONVERT(VARCHAR(MAX), comment) AS conv_comment
FROM your_table;

SELECT 
    *,
    REPLACE(
        LEFT(
            SUBSTRING(conv_comment, CHARINDEX('ABILITIES:', conv_comment) + 12, LEN(conv_comment)),
            CASE 
                WHEN CHARINDEX('.', SUBSTRING(conv_comment, CHARINDEX('ABILITIES:', conv_comment) + 12, LEN(conv_comment))) = 0
                    THEN 0
                ELSE CHARINDEX('.', SUBSTRING(conv_comment, CHARINDEX('ABILITIES:', conv_comment) + 12, LEN(conv_comment))) - 1 
            END
        ), ',', '') AS extracted_text
FROM @TempComments;

临时表示例 #

SELECT 
    *,
    CONVERT(VARCHAR(MAX), comment) AS conv_comment
INTO #TempComments
FROM your_table;

SELECT 
    *,
    REPLACE(
        LEFT(
            SUBSTRING(conv_comment, CHARINDEX('ABILITIES:', conv_comment) + 12, LEN(conv_comment)),
            CASE 
                WHEN CHARINDEX('.', SUBSTRING(conv_comment, CHARINDEX('ABILITIES:', conv_comment) + 12, LEN(conv_comment))) = 0
                    THEN 0
                ELSE CHARINDEX('.', SUBSTRING(conv_comment, CHARINDEX('ABILITIES:', conv_comment) + 12, LEN(conv_comment))) - 1 
            END
        ), ',', '') AS extracted_text
FROM #TempComments;

DROP TABLE #TempComments; -- 用完记得删除临时表

额外优化提示 #

• SQL Server中SUBSTRING的第三个参数如果超过字符串剩余长度，会自动取到末尾，因此可以直接用LEN(conv_comment)，无需计算复杂的长度差值。
• 如果ABILITIES:可能不存在，建议在逻辑中增加判断（比如CHARINDEX('ABILITIES:', conv_comment) > 0），避免SUBSTRING因起始位置为0而出错。

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