Doing a partial replace of a field

How could I do a replace of " " to " " in a string were the string is a
field and the values are variable. I would like to use Enterprise Manager o
r
a stored proc.
IE. Node.Nodename is equal to "have a nice day" I want to replace the "
" with " " after nice.> How could I do a replace of " " to " " in a string were the string is a
> field and the values are variable. I would like to use Enterprise Manager
> or
> a stored proc.
> IE. Node.Nodename is equal to "have a nice day" I want to replace the "
> " with " " after nice.
CREATE FUNCTION dbo.RegexReplace (
@.value VARCHAR(4000),
@.replacement VARCHAR(4000),
@.pattern VARCHAR(4000),
@.ignoreCase BIT
) RETURNS NVARCHAR(4000) AS
BEGIN
DECLARE @.hResult INT
DECLARE @.pRegex INT
EXECUTE @.hResult = sp_OACreate 'VBScript.RegExp', @.pRegex OUTPUT
EXECUTE @.hResult = sp_OASetProperty @.pRegex, 'Pattern', @.pattern
EXECUTE @.hResult = sp_OASetProperty @.pRegex, 'Global', TRUE
EXECUTE @.hResult = sp_OASetProperty @.pRegex, 'IgnoreCase', @.ignoreCase
EXECUTE @.hResult = sp_OAMethod @.pRegex, 'Replace', @.value OUTPUT, @.value,
@.replacement
EXECUTE @.hResult = sp_OADestroy @.pRegex
RETURN @.value
END
UPDATE YourTable
SET YourColumn = dbo.RegexReplace(YourColumn, ' ', '\s+', 1)
...My two ... But I'm kind of masochistic that way.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane