–Create a table with all bad codes
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=‘se+*ar%c&h^da#tab~se’
–Below command will replace bad codes with empty string.UPDATE #badcodes
SET @test=REPLACE(@test,CHAR(badcode),”)
PRINT @test
BLOG.JOGGEE 
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.
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
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
your code doesn’t work when the string contains the letters TH together. try CLOTH or MONTH
Bizzare
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
Nice Logic.
Thank you.
Hi,
Using your logic I have created a function named IsAlphaNumeric which returns 0 for Alpha-Numeric chars and 1 for non-alphanumeric chars.
Thanks again.
I have this as the input string +91-22-677980002.
Using your logic I get 96779800. Please advice.
Found it…..its actually 1,2 and 3 are present as superscript/exponent number too hance we need to exclude their codes 179,180,185. I hope this adds more spice to your already good logic 🙂
Nope… Bizzare is correct.
“your code doesn’t work when the string contains the letters TH together. try CLOTH or MONTH”
Pingback: 200 Words - Parsing Non-Alphanumeric Characters and Ligatures in SQL Server | With That Said
Pingback: Bereinigen einer Zeichenkette auf alphanumerische Zeichen via SQL | marcus' tagebuch
That was a great piece of code! I love it when programmers really think outside of the box and come-up with that short and sweet piece of miracle code.
Nice job!
Dear Friend
You are articles are nice and excellent concept. I would like to invite to newly launched .NET Programming website the codegain.com on 1st of this June 2009. Currently CodeGain has more than 400 articles within the a month under the followings categories C#, VB.NET,ASP.NET,WPF,WCF,WFF,LINQ, AJAX, JQuery, JavaScript, Sql Servers , Oracle and more. To more list of categories visit the http://www.codegain.com. I have seen you are writing greatest article to web portal, I’m kindly asking you publish your article in codegain.com also and support to grow the CodeGain share this with your friends also. I am expecting good response from you. You can contact me using info@codegain.com.
Thank you
RRaveen
codegain.com
Perfect!Great! This helped so much! I’ve read a couple
rather confusing sites lately, this cleared up a lot confusion I had.
Here’s a slightly different way, created as a function. I don’t recommend calling this function in a production app as this is highly innefficient.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RemoveJunk]’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’))
DROP FUNCTION [dbo].[RemoveJunk]
GO
/***********************************************************
REMOVES NON-STANDARD ASCII CHARACTERS FROM A STRING
***********************************************************/
CREATE FUNCTION RemoveJunk( @strIn AS varchar(max))
RETURNS varchar(max)
AS
BEGIN
DECLARE @i INT
SET @i=0
WHILE @i<256 — Check entire extended ascii set
BEGIN
— http://www.asciitable.com/
BEGIN
IF ((@i 127) AND @i 94) — List of characters to remove
BEGIN
SET @strIn=REPLACE(@strIn, char(@i), ”)
END
IF (@i IN (94)) — List of characters to replace with spaces
BEGIN
SET @strIn=REPLACE(@strIn, char(@i), ‘ ‘)
END
END
SET @i=@i+1
END
RETURN @strIn
END
Try Article Writing and Article Marketing to get your website noticed.