SQL Server Functions to Strip Unwanted Characters

-- -------------------------------------------------------------------------
-- SQL Server Functions to strip away unwanted characters from a string,
-- leaving only numeric, alpha, or alpha numeric values.
--
-- http://geekswithblogs.net/BenAdler/archive/2011/04/21/sql-functions-for-numeric-only-alpha-only-and-alpha-numeric.aspx
-- -------------------------------------------------------------------------

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-zA-Z]%', @string) > 0
    set @string = stuff(@string, patindex('%[^a-zA-Z]%', @string), 1, '');
    return @string;
end;
go

create function dbo.AlphaNumericOnly (@string varchar(max))
returns varchar(max)
begin
    while patindex('%[^a-zA-Z0-9]%', @string) > 0
    set @string = stuff(@string, patindex('%[^a-zA-Z0-9]%', @string), 1, '');
    return @string;
end;
go

-- -------------------------------------------------------------------------
-- Example Usage:
-- -------------------------------------------------------------------------

select dbo.NumericOnly('123ABC!!!') as [NumericOnly],
    dbo.AlphaOnly('123ABC!!!') as [AlphaOnly],
    dbo.AlphaNumericOnly('123ABC!@#') as [AlphaNumericOnly];