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

EF CodeFirst Database.SetInitializser Requires Connection to 'master' Database Error

One of the features of EF CodeFirst is the ability to automatically drop and recreate a database if the model changes or if the database does not exist, which is pretty useful when just doing development work. In a web app this is done in the Global.asax file within "protected void Application_Start()" as per this post from Scott Guthrie -  http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx . An example would be: protected void Application_Start() { Database.SetInitializer<yourdbcontex>(new DropCreateDatabaseIfModelChanges<yourdbcontext>()); AreaRegistration.RegisterAllAreas(); RegisterGlobalFilters(GlobalFilters.Filters); RegisterRoutes(RouteTable.Routes); } However when using the Database.SetInitializer and trying to rebuild the database, the following exception may be encountered, especially if using SQL Server Express: "This operation requires a connection to the 'master'...
4 comments
Read more

VWD Express 2010 Unit Tests with xUnit and MVC Templates

Microsoft's Visual Web Developer 2010 Express (VWD 2010 Express), by default does not come with a built in unit testing solution. However the ability is there to use unit tests with the Express version as can be seen when creating a new MVC project. Tech spec stuff: Microsoft Windows 7 Microsoft Visual Studio 2010 Express, Version 10.0.40219.1 SP1 Microsfot .NET Framework Version 4.0.30319 SP1 Getting started with xUnit xUnit is a "developer testing framework, built to support Test Driven Development (TDD)". There are other unit testing frameworks available (MSTest, nUnit, etc.). There are pro's to each and some better than others but xUnit is pretty straightforward to get up and running with the VWD 2010 Express version. Download and Install xUnit The xUnit project can be found at:  http://xunit.codeplex.com/ . Downloads are available from  http://xunit.codeplex.com/releases/view/62840 . Download the recommended version (currently 1.8 at time of writi...
Post a Comment
Read more