views:

892

answers:

5

Hey everyone,

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

+2  A: 

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

Sohnee
It may be that CLR can't be used in your environment
gbn
@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.
Ev
A: 

If it's a one-off thing, I would use a C# + LINQ snippet in LINQPad to do the job with regular expressions.

It is quick and easy and you don't have to go through the process of setting up a CLR stored procedure and then cleaning up after yourself.

Tiberiu Ana
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.
Ev
+1  A: 

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...

gbn
+2  A: 

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)
Tim C
@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?
Ev
+2  A: 

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

priyanka.sarkar
@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.
Ev