-- GSquared  

SSCrazy Eights Group: General Forum Members Last Login: Today @ 3:48:39 PM Points: 8,713, Visits: 4,977

--------------------------------------------------

CREATE function [dbo].[AlphaRemove]

(@String_in varchar(max))

returns varchar(max)

as

begin

/*

        *****   Takes a string variable and turns it into a set of 

        *****   numbers separated by spaces.

        *****   Despite the name, it also removes punctuation, not

        *****   just letters.

        *****   Input string must be simple ASCII, not Unicode.

        *****   (No accented letters, etc.)

*/

        declare @sub char(1)

--Letters

        while patindex('%[a-z]%', @string_in) > 0

        begin

                set @sub = substring(@string_in, patindex('%[a-z]%', @string_in), 1)

                set @string_in = replace(@string_in, @sub, '')

        end

--Punctuation

        while patindex('%[!-)]%', @string_in) > 0

        begin

                set @sub = substring(@string_in, patindex('%[!-/]%', @string_in), 1)

                set @string_in = replace(@string_in, @sub, '')

        end

        while patindex('%[+-/]%', @string_in) > 0

        begin

                set @sub = substring(@string_in, patindex('%[!-/]%', @string_in), 1)

                set @string_in = replace(@string_in, @sub, '')

        end

        while patindex('%[:-=]%', @string_in) > 0

        begin

                set @sub = substring(@string_in, patindex('%[:-@]%', @string_in), 1)

                set @string_in = replace(@string_in, @sub, '')

        end

        while patindex('%[?-@]%', @string_in) > 0

        begin

                set @sub = substring(@string_in, patindex('%[:-@]%', @string_in), 1)

                set @string_in = replace(@string_in, @sub, '')

        end

        set @string_in = replace(@string_in, '[', '')

        while patindex('%[\-`]%', @string_in) > 0

        begin

                set @sub = substring(@string_in, patindex('%[\-`]%', @string_in), 1)

                set @string_in = replace(@string_in, @sub, '')

        end

        while patindex('%[{-~]%', @string_in) > 0

        begin

                set @sub = substring(@string_in, patindex('%[{-~]%', @string_in), 1)

                set @string_in = replace(@string_in, @sub, '')

        end

        while charindex('  ', @string_in, 0) > 0

                set @string_in = replace(@string_in, '  ', ' ')

return @string_in

end