Re: using wildcards [ ] in REPLACE or CHARINDEX functions
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 11 May 2007 23:42:03 +0200
On Fri, 11 May 2007 08:15:00 -0700, Joe Sich wrote:
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,
Joe
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
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.
- Next by Date: Date reformat question for the group
- Next by thread: Date reformat question for the group
- Index(es):
Relevant Pages
|
|
