如何在SQL Server中提取不含数字的Student表address字段内容

嘿，我来帮你搞定这个需求！你要从Student表的address列（TEXT类型）里去掉所有数字，只保留纯文本内容，比如把TUCSON AZ 85705-7598 USA处理成TUCSON AZ USA对吧？咱们分情况来给你解决方案，还会提一些实用的小提醒~

首先得说一句：TEXT是SQL Server里的旧数据类型，微软官方更推荐用VARCHAR(MAX)来替代它，后者支持更多内置函数，处理起来更高效。如果你的业务允许，建议把address列的类型改成VARCHAR(MAX)，后续操作会更顺畅。不过如果暂时没法改，下面的方法也完全适用。

方法一：自定义标量函数（兼容所有SQL Server版本） #

先创建一个可以移除字符串中所有数字的函数，之后查询的时候直接调用就行，复用性很强：

CREATE FUNCTION dbo.RemoveNumbers (@InputText TEXT)
RETURNS VARCHAR(MAX)
AS
BEGIN
    -- 先把TEXT类型转成VARCHAR(MAX)，因为TEXT不支持修改操作
    DECLARE @OutputText VARCHAR(MAX) = CAST(@InputText AS VARCHAR(MAX))
    DECLARE @NumberPosition INT

    -- 循环查找并移除所有数字
    WHILE PATINDEX('%[0-9]%', @OutputText) > 0
    BEGIN
        SET @NumberPosition = PATINDEX('%[0-9]%', @OutputText)
        SET @OutputText = STUFF(@OutputText, @NumberPosition, 1, '')
    END

    -- 处理移除数字后可能出现的连续空格
    WHILE PATINDEX('%  %', @OutputText) > 0
    BEGIN
        SET @OutputText = REPLACE(@OutputText, '  ', ' ')
    END

    -- 去除首尾的多余空格
    SET @OutputText = LTRIM(RTRIM(@OutputText))

    RETURN @OutputText
END

创建好函数后，直接查询调用：

SELECT dbo.RemoveNumbers(address) AS CleanAddress
FROM Student

这个函数会帮你把所有数字删掉，还会自动清理掉多余的空格，完美得到你想要的结果~

方法二：递归CTE（无需创建函数，适合临时查询） #

如果不想创建函数，也可以用递归CTE来一次性处理，不过要注意递归深度的问题（默认最大递归深度是100，如果你的地址里数字超过100个，需要加OPTION (MAXRECURSION 0)）：

WITH CleanAddresses AS (
    SELECT 
        address,
        CAST(address AS VARCHAR(MAX)) AS TempAddress,
        1 AS Iteration
    FROM Student
    UNION ALL
    SELECT 
        address,
        STUFF(TempAddress, PATINDEX('%[0-9]%', TempAddress), 1, ''),
        Iteration + 1
    FROM CleanAddresses
    WHERE PATINDEX('%[0-9]%', TempAddress) > 0
)
SELECT 
    LTRIM(RTRIM(REPLACE(TempAddress, '  ', ' '))) AS CleanAddress
FROM CleanAddresses
WHERE PATINDEX('%[0-9]%', TempAddress) = 0
ORDER BY address, Iteration DESC
-- 如果地址中数字超过100个，加上下面这句
-- OPTION (MAXRECURSION 0)

方法三：简洁版（仅适用于SQL Server 2017及以上版本） #

如果你用的是2017或更新的版本，那可以用TRANSLATE函数来简化操作，不用循环或递归，效率更高：

SELECT 
    LTRIM(RTRIM(REPLACE(TRANSLATE(CAST(address AS VARCHAR(MAX)), '0123456789', '          '), '  ', ' '))) AS CleanAddress
FROM Student

原理很简单：TRANSLATE把每个数字都替换成一个空格，然后用REPLACE把连续的空格合并成一个，最后再去除首尾空格，一步到位~

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