SQL Server:如何从字段中删除标点符号? [英] SQL Server: How do you remove punctuation from a field?

问题描述

有人知道从 SQL Server 中的字段中删除标点符号的好方法吗?

Any one know a good way to remove punctuation from a field in SQL Server?

我在想

UPDATE tblMyTable SET FieldName = REPLACE(REPLACE(REPLACE(FieldName,',',''),'.',''),'''' ,'')

但是当我打算删除大量不同的字符时似乎有点乏味，例如:!@#$%^&*()<>:"

but it seems a bit tedious when I intend on removing a large number of different characters for example: !@#$%^&*()<>:"

提前致谢

推荐答案

理想情况下，您应该使用应用程序语言(例如上述 C# + LINQ)来执行此操作.

Ideally, you would do this in an application language such as C# + LINQ as mentioned above.

不过，如果您只想在 T-SQL 中完成此操作，一种使事情更整洁的方法是首先创建一个包含您要删除的所有标点符号的表.

If you wanted to do it purely in T-SQL though, one way make things neater would be to firstly create a table that held all the punctuation you wanted to removed.

CREATE TABLE Punctuation 
(
    Symbol VARCHAR(1) NOT NULL
)

INSERT INTO Punctuation (Symbol) VALUES('''')
INSERT INTO Punctuation (Symbol) VALUES('-')
INSERT INTO Punctuation (Symbol) VALUES('.')

接下来，您可以在 SQL 中创建一个函数来删除输入字符串中的所有标点符号.

Next, you could create a function in SQL to remove all the punctuation symbols from an input string.

CREATE FUNCTION dbo.fn_RemovePunctuation
(
    @InputString VARCHAR(500)
)
RETURNS VARCHAR(500)
AS
BEGIN
    SELECT
        @InputString = REPLACE(@InputString, P.Symbol, '')
    FROM 
        Punctuation P

    RETURN @InputString
END
GO

然后你可以在你的 UPDATE 语句中调用该函数

Then you can just call the function in your UPDATE statement

UPDATE tblMyTable SET FieldName = dbo.fn_RemovePunctuation(FieldName)

这篇关于SQL Server:如何从字段中删除标点符号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！