Comments on: Sql Server Tips – Removing or Replacing non-alphanumeric characters in strings

By: codeproject

codeproject — Fri, 14 Mar 2008 09:34:01 +0000

Mr. bizarre
It works fine, below is the example code for you.

DECLARE @mycode INT
CREATE TABLE #badcodes(badcode INT)
SET @mycode=33
WHILE @mycode<=255
BEGIN
IF (@mycode BETWEEN 33 AND 47) OR (@mycode BETWEEN

58 AND 64)
OR (@mycode BETWEEN 58 AND 64) OR (@mycode BETWEEN

91 AND 96)
OR (@mycode BETWEEN 123 AND 255)
INSERT INTO #BADCODES VALUES(@mycode)

SET @mycode=@mycode+1
END
GO
– Suppose you have a @test variable containing bad codes

DECLARE @test VARCHAR(100)
SET @test=’MONTH’
–Below command will replace bad codes with empty string.

UPDATE #badcodes
SET @test=REPLACE(@test,CHAR(badcode),”)
PRINT @test

DECLARE @test VARCHAR(100)
SET @test=’CLOTH’

–Below command will replace bad codes with empty string.
UPDATE #badcodes
SET @test=REPLACE(@test,CHAR(badcode),”)
PRINT @test

By: Chapman

Chapman — Thu, 13 Mar 2008 14:59:34 +0000

your code doesn’t work when the string contains the letters TH together. try CLOTH or MONTH

Bizzare

By: codeproject

codeproject — Sun, 11 Nov 2007 10:39:03 +0000

I will make it clear to you that first I created a temp table and insert all the special characters in it.

IF (@mycode BETWEEN 33 AND 47) OR (@mycode BETWEEN 58 AND 64)
OR (@mycode BETWEEN 58 AND 64) OR (@mycode BETWEEN 91 AND 96)
OR (@mycode BETWEEN 123 AND 255)

with this condition it will only let special character insert in a temp table.

In replace function I am using CHAR( function this will return Character code of the any alphabetic.

If you have any confusion do write me.

Rana

By: JC

JC — Sat, 10 Nov 2007 15:10:37 +0000

Thanks so much – Perfect! I’m just past the newbie stage of Transact-SQL and still learning. Can you please explain how the replace “knows” how to look at every badcode row in the table ? That’s a very powerful capability that would not be obvious from the Transact-SQL definition of REPLACE.

-JC

By: Vijayan

Vijayan — Wed, 19 Sep 2007 15:28:33 +0000

Wonderful. Pls keep me updated with your latest techniques. It really help me with my project.
Good luck. I’m looking forward to your next posting.