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

–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

Advertisements

17 thoughts on “Sql Server Tips – Removing or Replacing non-alphanumeric characters in strings

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

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

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

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

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

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

  7. Pingback: 200 Words - Parsing Non-Alphanumeric Characters and Ligatures in SQL Server | With That Said

  8. Pingback: Bereinigen einer Zeichenkette auf alphanumerische Zeichen via SQL | marcus' tagebuch

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s