Trim non alphanumeric characters but leave whitespace alon

Posted on 2013-10-30
Last Modified: 2013-10-30
Hello I'm using the following function and call to remove non alphanumeric characters from a string.

It does do this but also removes white space.

I need to remove all non alphanumeric characters while leaving white space for our product title field of our products table.

How can I adjust my function to leave the white space?

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)

    Declare @KeepValues as varchar(50) = '%[^a-z0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp

Open in new window

Select dbo.RemoveNonAlphaCharacters('This and that-:(_)')


Question by:gogetsome
LVL 26

Accepted Solution

Shaun Kline earned 500 total points
ID: 39612337
Adding a space in the square brackets works in SQL 2K8 R2

Declare @Temp varchar(100) = 'This and that-:(_)'
Declare @KeepValues as varchar(50) = '%[^a-z0-9 ]%'


While PatIndex(@KeepValues, @Temp) > 0
    Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')


Open in new window


Author Closing Comment

ID: 39612347
Awesome! That was a easy fix! Appreciate your time.

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

813 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

13 Experts available now in Live!

Get 1:1 Help Now