SQL Functions for Numeric Only, Alpha Only, and Alpha Numeric Only Characters

—These functions will strip away unwanted characters from a string, leaving only numeric, alpha, or alpha numeric values

create function dbo.NumericOnly(@string varchar(max))

returns varchar(max)

begin

   While PatIndex('%[^0-9]%', @string) > 0

        Set @string = Stuff(@string, PatIndex('%[^0-9]%', @string), 1, '')

return @string

end

 

GO --------------------------------

 

create function dbo.AlphaOnly(@string varchar(max))

returns varchar(max)

begin

   While PatIndex('%[^a-z]%', @string) > 0

        Set @string = Stuff(@string, PatIndex('%[^a-z]%', @string), 1, '')

return @string

end

 

GO --------------------------------

 

create function dbo.AlphaNumericOnly(@string varchar(max))

returns varchar(max)

begin

   While PatIndex('%[^a-z0-9]%', @string) > 0

        Set @string = Stuff(@string, PatIndex('%[^a-z0-9]%', @string), 1, '')

return @string

end

 

GO --------------------------------

 

select

    dbo.NumericOnly('123ABC!!!') as [NumericOnly],

    dbo.AlphaOnly('123ABC!!!') as [AlphaOnly],

    dbo.AlphaNumericOnly('123ABC!@#') as [AlphaNumericOnly]