如何在SQL Server中从字符串中剥离所有非字母字符? [英] How to strip all non-alphabetic characters from string in SQL Server?

问题描述

如何从字符串中删除所有非字母字符?

How could you remove all characters that are not alphabetic from a string?

那非字母数字呢?

这是否必须是自定义函数，或者是否还有更通用的解决方案?

Does this have to be a custom function or are there also more generalizable solutions?

推荐答案

尝试使用此功能:

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

这样称呼:

Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')

一旦理解了代码，您应该会看到，更改代码以删除其他字符也相对简单.您甚至可以使它具有足够的动态性，以传递您的搜索模式.

Once you understand the code, you should see that it is relatively simple to change it to remove other characters, too. You could even make this dynamic enough to pass in your search pattern.

希望有帮助.

这篇关于如何在SQL Server中从字符串中剥离所有非字母字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！