SQL Server: How to Remove Special Characters

 

CREATE FUNCTION dbo.RemoveSpecialChars ( @InputString VARCHAR(8000) )
RETURNS VARCHAR(8000)  
BEGIN
    IF @InputString IS NULL
        RETURN NULL
    DECLARE @OutputString VARCHAR(8000)
    SET @OutputString = ''
    DECLARE @l INT
    SET @l = LEN(@InputString)
    DECLARE @p INT
    SET @p = 1
    WHILE @p <= @l
        BEGIN
            DECLARE @c INT
            SET @c = ASCII(SUBSTRING(@InputString, @p, 1))
            IF @c BETWEEN 48 AND 57
                OR @c BETWEEN 65 AND 90
                OR @c BETWEEN 97 AND 122
                  --OR @c = 32
                SET @OutputString = @OutputString + CHAR(@c)
            SET @p = @p + 1
        END
    IF LEN(@OutputString) = 0
        RETURN NULL
    RETURN @OutputString
END

How to use it.
SELECT dbo.RemoveSpecialChars ('This string contains special chracters:/ Which * & we % need @ to #remove')

According to this function space is a special character and if you want to ignore spaces then uncomment “OR @c = 32”.

Comments

Post a Comment

Popular posts from this blog

How to create RSS Feed in Asp.Net

using System; using System.Data; using System.Configuration; using System.Data.SqlClient; using System.Text; using System.Xml; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString; DataTable dt = new DataTable(); SqlConnection conn = new SqlConnection(connectionString); using (conn) { SqlDataAdapter ad = new SqlDataAdapter("SELECT * from tblRSS", conn); ad.Fill(dt); } Response.Clear(); Response.ContentType = "text/xml"; XmlTextWriter TextWriter = new XmlTextWriter(Response.OutputStream, Encoding.UTF8); TextWriter.WriteStartDocument(); //Below tags are mandatory rss tag TextWriter.WriteStartElement("rss"); TextWriter.WriteAttributeString("versio...
Read more

Auto generate change scripts in SQL Server Management Studio SSMS for tables

Image
Problem As a part of my best practices, I always save the T-SQL scripts used for creation and modification of objects in SQL Server. When creating and modifying tables using SQL Server Management Studio designer it is easy to right click in the designer and select "Generate Change Script...", but is there a way to automatically script the creation and/or modification of tables made through of SQL Server Management Studio (SSMS) designer?  This tip shows you an option that exists within SSMS to automatically generate these scripts for all table changes when using the table designer. Solution Within SQL Server Management Studio (SSMS) there is an option to automatically generate scripts for your changes done through the GUI.  The solution for this problem is provided in SSMS and works with both SQL Server 2005 and SQL Server 2008, but by default this option that we will discuss is disabled. To enable the option From the SSMS menus click on "Tools"  ...
Read more

HOW TO Use the NumericUpDown Control

HOW TO Use the NumericUpDown Control  Great help taken from  http://www.asp.net/ajaxlibrary/HOW%20TO%20Use%20the%20NumericUpDown%20Control.ashx NumericUpDown Demonstration NumericUpDown Description NumericUpDown is an ASP.NET AJAX extender that can be attached to an ASP.NET TextBox control to add "up" and "down" buttons that increment and decrement the value in the TextBox. The increment and decrement can be simple +1/-1 arithmetic, they can cycle through a provided list of values (like the months of the year), or they can call a Web Service to determine the next value. Page authors can also provide custom images to be used instead of the default up/down button graphics. Video - How Do I: Use the ASP.NET AJAX NumericUpDown Control? NumericUpDown Client Code Sample view plain copy to clipboard print ? < script   runat = "server" >        [System.Web.Services.W...
Read more