SQL Server: How do you remove punctuation from a field?

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

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('.')

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)ASBEGIN    SELECT        @InputString = REPLACE(@InputString, P.Symbol, '')    FROM         Punctuation P    RETURN @InputStringENDGO

Then you can just call the function in your UPDATE statement

UPDATE tblMyTable SET FieldName = dbo.fn_RemovePunctuation(FieldName)

I wanted to avoid creating a table and wanted to remove everything except letters and digits.

DECLARE @p intDECLARE @Result Varchar(250)DECLARE @BadChars Varchar(12)SELECT @BadChars = '%[^a-z0-9]%'-- to leave spaces - SELECT @BadChars = '%[^a-z0-9] %'SET @Result = @InStrSET @P =PatIndex(@BadChars,@Result)WHILE @p > 0 BEGIN    SELECT @Result = Left(@Result,@p-1) + Substring(@Result,@p+1,250)    SET @P =PatIndex(@BadChars,@Result)    END

I am proposing 2 solutions

Solution 1: Make a noise table and replace the noises with blank spaces

e.g.

DECLARE @String VARCHAR(MAX)DECLARE @Noise TABLE(Noise VARCHAR(100),ReplaceChars VARCHAR(10))SET @String = 'hello! how * > are % u (: . I am ok :). Oh nice!'INSERT INTO @Noise(Noise,ReplaceChars)SELECT '!',SPACE(1) UNION ALL SELECT '@',SPACE(1) UNION ALLSELECT '#',SPACE(1) UNION ALL SELECT '$',SPACE(1) UNION ALLSELECT '%',SPACE(1) UNION ALL SELECT '^',SPACE(1) UNION ALLSELECT '&',SPACE(1) UNION ALL SELECT '*',SPACE(1) UNION ALLSELECT '(',SPACE(1) UNION ALL SELECT ')',SPACE(1) UNION ALLSELECT '{',SPACE(1) UNION ALL SELECT '}',SPACE(1) UNION ALLSELECT '<',SPACE(1) UNION ALL SELECT '>',SPACE(1) UNION ALLSELECT ':',SPACE(1)SELECT @String = REPLACE(@String, Noise, ReplaceChars) FROM @NoiseSELECT @String Data

Solution 2: With a number table

DECLARE @String VARCHAR(MAX)SET @String = 'hello! & how * > are % u (: . I am ok :). Oh nice!';with numbercte as( select 1 as rn union all select rn+1 from numbercte where rn<LEN(@String))select REPLACE(FilteredData,'&#x20;',SPACE(1)) Data from (select SUBSTRING(@String,rn,1) from numbercte  where SUBSTRING(@String,rn,1) not in('!','*','>','<','%','(',')',':','!','&','@','#','$')for xml path(''))X(FilteredData)

Output(Both the cases)

Data

hello  how   are  u  . I am ok . Oh nice

Note- I have just put some of the noises. You may need to put the noises that u need.

Hope this helps