SQL – How to strip out all non-alphabetic characters from string

Create the following function to strip out non-alphabetic characters from a string in a SQL statement:

CREATE Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
While PatIndex(‘%[^a-z]%’, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(‘%[^a-z]%’, @Temp), 1, ”)

Return @TEmp
End

Call the function using the following syntax:

select RemoveNonAlphaCharacters(‘qwerty123uiop56789’)

returns:

qwertyuiop

 

You can allow numbers 1-9  along with letters a – z by replacing the PatIndex with ‘%[^a-z0-9]%’

If you want to allow letters a – z, numbers 0 – 9 and other characters such as underscore the syntax is ‘%[^a-z0-9_]%’

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s