Trim Non-Alpha characters from string

You want to trim non-alpha characters from your string, here is the function. This function takes a string parameter and returns a string with only alpha characters.



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: Atif Sheikh
-- Create date: 28-05-2010
-- Description: Trim Non Alpha Characters
-- =============================================
--Select dbo.fnTrimNonAlphaCharacters('Atif Sheikh')
ALTER FUNCTION [dbo].[fnTrimNonAlphaCharacters]
(
@pString varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
Declare @vTable Table (Ch Char(1))
Declare @vRetString varchar(max)

Insert Into @vTable
SELECT SUBSTRING(@pString,N,1)
FROM dbo.tblTally a
WHERE N <= LEN(@pString) ORDER BY N

Set @vRetString = ''

Select @vRetString = @vRetString + Ch
from @vTable
Where Ascii(Ch) between 97 and 122
Or Ascii(Ch) between 65 and 90
Or Ascii(Ch) = 32

Return @vRetString

-- Select Ascii(' ')
END