USE [Common]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER 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

It's pretty RBAR, but it does work. One of these days, I'll rewrite it to be set-based. (Originally wrote this thing years ago. Was clueless on SQL.)