sql server mseq: I'm basically trying to replace non alpha characters of values in a couple
columns, but would prefer to create a ud-function or have a function in my
view do this for me rather than have to manually update every non-alpha
character.

I'm trying to do something like this:

REPLACE(Col001,[^ABCD...XYZ],' ')

In my mind, it reads replace any character in Col001 outside of the range of
A-Z with a space. But apparently I can only use wildcards with a LIKE? Did
I just answer my own question?

Anyone have any ideas how to accomplish this without explicitly stating all
non-alpha characters in an update query?

Thanks,

[quoted text, click to view]

Hi Joe,

Unfortunately, that's not possible. SQL Server's string manipulation
functions are somewhat limited; this is one of the things they lack.

The fastest, though clumsy to write and maintain, is probably a
humongous nested REPLACE:

REPLACE(REPLACE(...(REPLACE(Col001, '!', ''), '@', ''), ...), '#', '')

Cleaner, but slower, would be to code a userdefined function that uses a
loop like this:

SET @pos = PATINDEX('%[^A-Z]%', UPPER(@String));
WHILE @pos > 0
BEGIN;
SET @String = REPLACE(@String, SUBSTRING(@String, @pos, 1), '');
SET @pos = PATINDEX('%[^A-Z]%', UPPER(@String));
END;


If you're using SQL Server 2005, an alternative might be to create a CLR
userdefined function that uses existing string manipulation functions of
the .Net framework to remove non alpha characters from the string. This
will definitely be faster than the T-SQL version with a loop and might
even be faster than the nested REPLACE maintenance nightmare.

--
Hugo Kornelis, SQL Server MVP