Stripping Punctuation

[imani_technology_spam]

Has anyone had to deal with removing punctuation (commas, apostrophes,
etc.) from a column? What is the most efficient way to have these
characters automatically removed from the column?

[SQL]

use replace
update table set field = replace(field,',','') --commas
update table set field = replace(field,'.','') -- dots
update table set field = replace(field,'''','') --apostrophes
you can put this in a trigger if you want to do this automatically

------------------------------------------------------------------------------------------
"I sense many useless updates in you... Useless updates lead to
defragmentation... Defragmentation leads to downtime...Downtime leads
to suffering..Defragmentation is the path to the darkside.. DBCC
INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with
you" --
http://sqlservercode.blogspot.com/

[Hugo Kornelis]

On 24 Oct 2005 11:43:32 -0700, SQL wrote:

use replace
update table set field = replace(field,',','') --commas
update table set field = replace(field,'.','') -- dots
update table set field = replace(field,'''','') --apostrophes

Hi SQL,

More efficient to do it all in one pass:

UPDATE table
SET field = REPLACE(REPLACE(REPLACE(field,',',''),'.',''),'''' ,'')

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[imani_technology_spam]

Thanks!

Hugo Kornelis wrote:

On 24 Oct 2005 11:43:32 -0700, SQL wrote:

use replace
update table set field = replace(field,',','') --commas
update table set field = replace(field,'.','') -- dots
update table set field = replace(field,'''','') --apostrophes

Hi SQL,

More efficient to do it all in one pass:

UPDATE table
SET field = REPLACE(REPLACE(REPLACE(field,',',''),'.',''),'''' ,'')

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)