Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Trim non alphanumeric characters but leave whitespace alon

Posted on 2013-10-30
2
Medium Priority
?
287 Views
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)
AS
Begin

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

    Return @Temp
End

Open in new window




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

returns

Thisandthat
0
Comment
Question by:gogetsome
2 Comments
 
LVL 27

Accepted Solution

by:
Shaun Kline earned 2000 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 ]%'

PRINT @Temp

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

PRINT @Temp

Open in new window

0
 

Author Closing Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

963 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