0
Select dbo.[RemoveNonAlphaNumExceptSub]('aAbc123-4def5678ghi90 jkl#^.\')
  • escaping '%[^a-z0-9-]%' returns aAbc123-4def5678ghi90jkl\
  • before '%[-^a-z0-9]%' returns #.\ adding in beginning, end they hyphen but it still gets stripped

How to strip all non-alphabetic characters from string in SQL Server?

Create Function [dbo].[RemoveNonAlphaNumExceptSub]( @Temp VarChar( max ) )
Returns VarChar( 1000 )
AS
Begin

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

Return @Temp
End
Vinh Ton
  • 107
  • 3
  • 10

2 Answers2

0

Try escaping it by placing a backslash in front of the regex pattern like: '%[^a-z0-9\-]%'.

Alternatively, you can place the hyphen as the first character in the pattern like '%[-^a-z0-9]%'

Feel free to refer to this post for more help, or refer to this post too.

PausePause
  • 746
  • 2
  • 9
  • 21
0

this finally works '%[^a-z0-9-]%'

Vinh Ton
  • 107
  • 3
  • 10