sql query remove special characters from a string

Posted on 2011-04-30
Last Modified: 2012-05-11
I want to remove special characters from a string in sql query
to @mystring="ddssss"
@mystring="jon doe"
to @mystring="jondoe"
Question by:Angela4eva
    LVL 24

    Expert Comment


    for this you can use the T-SQL function "Replace". You would need one Replace call for each character (or set of characters). Here is a solution where the author used a match table to look for the wanted replacement characters and replace it in a target table:

    Replace data of one table with data of other table

    This is a lot more comfortable as you only need to change the records in the match table if you want to replace different, more or less characters without changing the code.


    LVL 22

    Expert Comment

    by:Om Prakash
    Example for your variables & data:

    DECLARE @mystring VARCHAR(50)
    SET @mystring = 'ddss$ss'
    SET @mystring = REPLACE(@mystring, '$', '')
    SELECT @mystring

    DECLARE @mystring2 VARCHAR(50)
    SET @mystring2 = 'jon doe'
    SET @mystring2 = REPLACE(@mystring2, ' ', '')
    SELECT @mystring2
    LVL 40

    Expert Comment

    How many special characters do you have?
    LVL 44

    Expert Comment

    Upper Case, Numbers ??

    Any character not like [a-z] ??

    I suggest you loop through each character in the string and if it doesn't pass the test above, append a null, else the character.
    LVL 9

    Accepted Solution

    This is a function you can use to do this , Credit goes to the author from here
    -- Removes special characters from a string value.
    -- All characters except 0-9, a-z and A-Z are removed and
    -- the remaining characters are returned.
    -- Author: Christian d'Heureuse,
    create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256)
       with schemabinding
       if @s is null
          return null
       declare @s2 varchar(256)
       set @s2 = ''
       declare @l int
       set @l = len(@s)
       declare @p int
       set @p = 1
       while @p <= @l begin
          declare @c int
          set @c = ascii(substring(@s, @p, 1))
          if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122
             set @s2 = @s2 + char(@c)
          set @p = @p + 1
       if len(@s2) = 0
          return null
       return @s2
    Example of how to use the function:
      select dbo.RemoveSpecialChars('abc-123+ABC')Result:

    Open in new window


    Author Closing Comment

    great solution

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now