基于T-SQL实现作物名称字符串转换及自定义函数开发的技术需求

我来帮你搞定这个作物名称的字符串转换需求，先给你直接可用的查询语句，再提供修正后的自定义函数，顺便说下你原来函数里的问题：

假设你的作物表名为Crops，作物名称列是CropName，可以直接用下面的查询生成转换后的列：

SELECT 
    CropName,
    'crop.groups.' + REPLACE(REPLACE(LOWER(CropName), ' and ', ' '), ' ', '_') AS TranslatedCropKey
FROM Crops;

这个查询的逻辑拆解：

• 先用LOWER()把整个字符串转为小写
• 用REPLACE(CropName, ' and ', ' ')精准移除" and "（前后带空格，避免误删类似"sand"里的"and"片段），替换成单个空格保证后续处理不会出现多个连续下划线
• 再用REPLACE(..., ' ', '_')把所有空格替换为下划线
• 最后拼接前缀crop.groups.

你原来的函数用逐字符循环的方式效率低，而且逻辑上有错误（单个字符不可能匹配%and%），我重写了一个更简洁高效的版本：

CREATE FUNCTION dbo.TranslateCropKey (@CropName NVARCHAR(1000))
RETURNS NVARCHAR(1000)
AS
BEGIN
    DECLARE @ProcessedStr NVARCHAR(1000);
    
    -- 1. 转换为小写
    SET @ProcessedStr = LOWER(@CropName);
    -- 2. 移除" and "（保留前后空格替换为单个空格，避免多空格）
    SET @ProcessedStr = REPLACE(@ProcessedStr, ' and ', ' ');
    -- 3. 把所有空格替换为下划线
    SET @ProcessedStr = REPLACE(@ProcessedStr, ' ', '_');
    -- 4. 添加指定前缀
    SET @ProcessedStr = CONCAT('crop.groups.', @ProcessedStr);
    
    RETURN @ProcessedStr;
END;

使用这个函数的示例：

SELECT 
    CropName,
    dbo.TranslateCropKey(CropName) AS TranslatedCropKey
FROM Crops;

测试你给的示例输入Beverages and spice crops，会正确输出crop.groups.beverages_spice_crops。

帮你梳理下原来函数里的几个关键问题：

• 逐字符循环效率低下：SQL的内置字符串函数专门处理这类批量文本操作，比逐字符循环性能好很多
• "and"移除逻辑错误：你在循环里取单个字符@str，然后判断@str LIKE '%and%'，单个字符根本不可能匹配包含三个字符的"and"，所以这部分代码完全不起作用
• 参数类型不合理：原函数参数用nchar(15)，会截断超过15个字符的输入，应该用NVARCHAR(1000)和返回类型保持一致
• 空格处理后会留多余下划线：因为"and"没被正确移除，原来的"Fruit and Nuts"会被处理成fruit_and_nuts，而不是期望的fruit_nuts

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