Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Trim non alphanumeric characters but leave whitespace alon

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
gogetsome
Asked:
gogetsome
1 Solution
 
Shaun KlineLead Software EngineerCommented:
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
 
gogetsomeAuthor Commented:
Awesome! That was a easy fix! Appreciate your time.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now