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

sql remove non alphanumeric characters
how to remove punctuation from a column in sql
sql replace
sql server regex
remove punctuation ssis
remove in sql
regex remove punctuation
sql remove word from string

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

I'm thinking

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: !@#$%^&*()<>:"

Thanks in advance

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

    RETURN @InputString
END
GO

Then you can just call the function in your UPDATE statement

UPDATE tblMyTable SET FieldName = dbo.fn_RemovePunctuation(FieldName)

Removing punctuation from a string – SQLServerCentral, I.E. I'm looking for something like a function that will strip out anything There was an article on SQL Server Central about a very similar topic  Summary: in this tutorial, you will learn how to use the SQL Server ALTER TABLE DROP column statement to remove one or more columns from existing table. Sometimes, you need to remove one or more unused or obsolete columns from a table. To do this, you use the ALTER TABLE DROP COLUMN statement as follows: If the column that you want to delete

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

DECLARE @p int
DECLARE @Result Varchar(250)
DECLARE @BadChars Varchar(12)
SELECT @BadChars = '%[^a-z0-9]%'
-- to leave spaces - SELECT @BadChars = '%[^a-z0-9] %'

SET @Result = @InStr

SET @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

Basic query to remove punctuation in table field, I'm new to SQL and I need help with a very basic query to remove punctuation from one of our database tables. Basically, we have a table  SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

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 ALL
SELECT '#',SPACE(1) UNION ALL SELECT '$',SPACE(1) UNION ALL
SELECT '%',SPACE(1) UNION ALL SELECT '^',SPACE(1) UNION ALL
SELECT '&',SPACE(1) UNION ALL SELECT '*',SPACE(1) UNION ALL
SELECT '(',SPACE(1) UNION ALL SELECT ')',SPACE(1) UNION ALL
SELECT '{',SPACE(1) UNION ALL SELECT '}',SPACE(1) UNION ALL
SELECT '<',SPACE(1) UNION ALL SELECT '>',SPACE(1) UNION ALL
SELECT ':',SPACE(1)

SELECT @String = REPLACE(@String, Noise, ReplaceChars) FROM @Noise
SELECT @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

Remove punctuation marks, special chars and space and compare , I am first trying to see if I can query the columns with the values replaced and then use it in an update statement and have the column value  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

You can use regular expressions in SQL Server - here is an article based on SQL 2005:

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

♑️ 🥒 🤾🏿 SQL Server: how to remove punctuation from a field?, Ideally, you should do this in an application language such as C # + LINQ, as described above. If you want to do this exclusively in T-SQL, one way to make  Take a look at the following function - REPLACE(): If you want to remove a string, simple use the replace function with an empty string as the third parameter like: @ichiban - I'm still hoping that is something SQL corrects. It'd be nice for that to be consistent considering the single quote can comment out VB code

I'd wrap it in a simple scalar UDF so all string cleaning is in one place if it's needed again.

Then you can use it on INSERT too...

https://social.msdn.microsoft.com/Forums/en-US/6fe, Stripping Punctuation. Microsoft SQL Server Forums on Bytes. Has anyone had to deal with removing punctuation (commas, apostrophes, etc.) from a column? What is the most update table set field = replace(field,'''','') --apostrophes you can put this in a trigger if you want to do this automatically You can use REPLACE function to replace commas with something else. UPDATE table. SET column = REPLACE(column, ‘,’,’<something else>’) WHERE <condition that selects the right rows to apply REPLACE to>. REPLACE (Transact-SQL)

Stripping Punctuation - Microsoft SQL Server, Any one know a good way to remove punctuation from a field in SQL Server? I'm thinking. ASP.NET Forums / Data Access / SQL Server, SQL Server Express, and SQL Compact Edition / How to remove special char(,) from a string in sql server 2008 How to remove special char(,) from a string in sql server 2008 [Answered] RSS

SQL Server TRIM() Function, SQL Server TRIM() Function. ❮ SQL Server Functions Remove leading and trailing spaces from a string: The string to remove spaces or characters from  I'm new to SQL and I need help with a very basic query to remove punctuation from one of our database tables. Basically, we have a table called PATIENTS and in this table there is a field called LAST NAME where at the end of every patients last name there is a * I need to remove the * for 5000+ last names and I want to write a query to do this.

Any one know a good way to remove punctuation from a field in SQL Server? I'm thinking. UPDATE tblMyTable SET FieldName 

Comments
  • @TimC: Thanks a lot I liked this solution and implemented it, but came up with a problem. Once I take the punctuation out I intend on using it in a computed column that is persisted so I can create a Full Text Index on it. However I can't persist the column that uses this method. Any ideas?
  • @pewned. Thanks a lot. I ended up using a different idea, but this worked and answered my question. One question for you though, I need to use this data in a computed column that is persisted, but I can't persist it there. Any ideas? I'll probably make a new question for this. Thanks again.
  • It may be that CLR can't be used in your environment
  • @Sohnee - thanks for the fast reply. I've not tried this method yet, because I need to use this data in a Full Text Index, and I'm thinking if I use this I won't be able to make the column persistent. Is that correct? Although I'm definitely going to try something like that next time its' feasible. Thanks.
  • Thanks a lot, it's not a one of thing in this case, but that's a good looking tool for me to use in the future. Cheers.