Skip to main content

SQL Server - Remove Non-Alphanumeric Characters from String

The following SQL function will remove and strip all non-alphanumeric characters from a string.


CREATE FUNCTION [dbo].[fncRemoveNonAlphanumericChars](@Temp VarChar(1000))
RETURNS VarChar(1000)
AS
BEGIN
WHILE PatIndex('%[^A-Za-z0-9]%', @Temp) > 0
SET @Temp = Stuff(@Temp, PatIndex('%[^A-Za-z0-9]%', @Temp), 1, '')

RETURN @TEmp
END

Example:

SELECT dbo.fncRemoveNonAlphanumericChars('abc...DEF,,,GHI(((123)))456jklmn')

Result:

abcDEFGHI123456jklmn
Labels:

Comments

Post a Comment

Popular posts from this blog

Entity Framework Code-First Learning Resources

Some resources to help learn and understand Microsoft's Entity Framework Code-First ORM. Basic Tutorial http://www.asp.net/mvc/tutorials/getting-started-with-mvc3-part1-cs Advanced Tutorial http://www.asp.net/mvc/tutorials/mvc-music-store-part-1 Blog posts with further hints and tips http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx Update to NerdDinner using EF Code-First. Also shows how to synchronise database with the model and seeding database with initial data. http://www.hanselman.com/blog/SimpleCodeFirstWithEntityFramework4MagicUnicornFeatureCTP4.aspx Some useful info demonstrating Data Annotations such as [Key] and [Required]. This post will be added to as time goes by. If there are any other good resources about this MS framework feel free to throw them in to a comment. Updates EF Feature CTP5: Code First Walkthrough http://blogs.msdn.com/b/adonet/archive/2010/12/06/ef-feature-ctp5-code-first-walkthrou
Post a Comment
Read more

Which blog engine?

So the time has come to move to a more advanced blog engine for my blog. blogger.com , Google's blogging service, has served me well. It's incredibly easy to use and to get started with, along with having some great features such as inbuilt stats; however now I need a few more advanced features and greater control over the blog. There's a vast array of blog engines out there, some free, some paid for, some hosted, some self-hosted, and picking which one is best or the right choice could be a little bit tricky. This article from Mashable lists most of the main options and bigger players -  http://mashable.com/2007/08/06/free-blog-hosts/ . There are a few parameters that I've kind of decided on Ease of installation/compatibility and support with web hosts Simple to use. I don't want to spend ages clicking around just to add a post or format it. Feature rich and well supported. Most blog engines should have a fairly standard set of features now such RSS/ATOM fe
2 comments
Read more

Enable .NET 8 Preview in Visual Studio

Download the SDK using Download .NET 8.0 (Linux, macOS, and Windows) (microsoft.com)  and install it. To enable projects to target the .NET 8 preview framework, the preview option in Visual Studio needs to be enabled, otherwise the option to target .NET 8 will not be available as shown below when setting up a new project (or trying to upgrade an existing one). To allow .NET 8 Preview to be used as a target framework for projects, the preview option needs to be enabled in Visual Studio. Open Visual Studio and select "Continue without code" In Visual Studio, select Tools then Options In Options, under Environment, select Preview Features and enable Use previews of the .NET SDK.
Read more