Home Forums SQL Server 2005 T-SQL (SS2K5) How to Remove Special Characters except Space RE: How to Remove Special Characters except Space

  • March 10, 2015 at 6:23 pm

    #1783108

    suresh0534 (3/9/2015)

    Hi,

    I got the Solution.

    Below is the Solution.

    DECLARE @STR VARCHAR(400)

    --Add your specialcharacters here

    DECLARE @specialchars VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!^?:]%'

    SET @STR = 'KRA!NTHI@#KUMAR, KU%^?MAR GO~()$U.:D'

    WHILE PATINDEX( @specialchars, @STR ) > 0

    ---Remove special characters using Replace function

    SET @STR = Replace(REPLACE( @STR, SUBSTRING( @STR, PATINDEX( @specialchars, @STR ), 1 ),''),'-',' ')

    SELECT @STR

    You don't need commas between every character in @SpecialCharacters. Also, what will you do for special characters that you've left out and the special characters that don't even show up on the usual 101 style keyboard? Last but not least, how are you proposing cleaning a whole column of strings?

    I know how to do those things but I'm interested in what your solution would be for those things.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)