如何将SQL查询返回的数字计数转换为英文单词形式？

1. 创建数字转英文单词的自定义函数 #

SQL Server没有内置的数字转英文单词的功能，你可以创建一个自定义标量函数来实现这个需求。以下是支持0-999范围的示例函数：

CREATE FUNCTION dbo.NumberToWords(@Number INT)
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @Words VARCHAR(100) = ''
    
    -- 处理0的特殊情况
    IF @Number = 0
        RETURN 'zero'
    
    -- 定义基础数字对应英文词
    DECLARE @Ones TABLE (Num INT, Word VARCHAR(20))
    INSERT INTO @Ones VALUES
        (1,'one'),(2,'two'),(3,'three'),(4,'four'),(5,'five'),
        (6,'six'),(7,'seven'),(8,'eight'),(9,'nine'),(10,'ten'),
        (11,'eleven'),(12,'twelve'),(13,'thirteen'),(14,'fourteen'),
        (15,'fifteen'),(16,'sixteen'),(17,'seventeen'),(18,'eighteen'),
        (19,'nineteen')
    
    DECLARE @Tens TABLE (Num INT, Word VARCHAR(20))
    INSERT INTO @Tens VALUES
        (20,'twenty'),(30,'thirty'),(40,'forty'),(50,'fifty'),
        (60,'sixty'),(70,'seventy'),(80,'eighty'),(90,'ninety')
    
    -- 处理百位
    IF @Number >= 100
    BEGIN
        SELECT @Words += Word + ' hundred ' FROM @Ones WHERE Num = @Number / 100
        SET @Number = @Number % 100
        IF @Number > 0
            SET @Words += 'and '
    END
    
    -- 处理十位和个位
    IF @Number >= 20
    BEGIN
        SELECT @Words += Word FROM @Tens WHERE Num = (@Number / 10) * 10
        SET @Number = @Number % 10
        IF @Number > 0
            SET @Words += '-' + (SELECT Word FROM @Ones WHERE Num = @Number)
    END
    ELSE IF @Number > 0
    BEGIN
        SELECT @Words += Word FROM @Ones WHERE Num = @Number
    END
    
    RETURN LTRIM(RTRIM(@Words))
END
GO

2. 在触发器中调用函数并发送邮件 #

修改触发器逻辑，先获取插入记录数，调用函数转换为英文单词，再拼接邮件内容发送：

CREATE TRIGGER trg_AfterInsert
ON [table name] -- 替换为你的表名
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 获取插入的记录数量
    DECLARE @RecordCount INT = @@ROWCOUNT;
    -- 转换为英文单词
    DECLARE @CountWords VARCHAR(100) = dbo.NumberToWords(@RecordCount);
    
    -- 拼接邮件内容
    DECLARE @EmailBody VARCHAR(500) = 'There are ' + @CountWords + ' record(s) inserted into the table.';
    
    -- 发送Database Email
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Your_Email_Profile', -- 替换为你的邮件配置文件名
        @recipients = 'recipient@example.com', -- 替换为实际收件人邮箱
        @subject = 'Record Insert Notification',
        @body = @EmailBody;
END
GO

补充说明 #

• 如果你需要处理更大范围的数字（如千、万级），可以扩展上述函数的逻辑，添加千位、百万位等对应英文词的处理。
• 你之前尝试的ASCII函数是用于获取字符的ASCII编码，和数字转英文单词的需求无关，自定义函数是更直接的解决方案。

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