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

Posted by Joggee | SQL Tips and Tricks | Tuesday 18 September 2007 6:04 pm

–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

5 Comments »

  1. Comment by Vijayan — September 19, 2007 @ 3:28 pm

    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. Comment by JC — November 10, 2007 @ 3:10 pm

    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. Comment by codeproject — November 11, 2007 @ 10:39 am

    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. Comment by Chapman — March 13, 2008 @ 2:59 pm

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

    Bizzare

  5. Comment by codeproject — March 14, 2008 @ 9:34 am

    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

RSS feed for comments on this post. TrackBack URI

Leave a comment