Posted by mirandacascade on 11/05/07 20:32

Situation is this:
1) I 'own' a stored procedure
2) I do not own the applications/processes that call stored procedure
3) stored procedure accepts varchar parameter
4) circumstances have changed such that
a) what used to be a valid parameter is no longer valid
b) there is a very well-defined pattern for identifying/correcting
the 'no longer valid'
c) the correcting measure involves:
-- detecing if the parameter has a certain subtring pattern
-- if so, changing that substring to a different substring
5) the applications/processes that call the stored procedure will not
change the value of the parameter that they send to stored procedure

So, given those circumstances, I'm hoping for best practices advice
about how to transform a varchar parameter into a new varchar
variable. For example:

CREATE PROCEDURE foo
@strValFromFrontEnd varchar[50]=''

AS

DECLARE
@strTransformedVal varchar[50]

-- whenever @strValFromFrontEnd contains the substring 'abc',
transform it such that
-- it gets replaced by '123'; examples:
-- @strValFromFrontEnd @strTransformedVal
-- -------------------------------- ------------------------------
-- four score abc and four score 123 and
-- hello abc world abc yo hello 123 world 123 yo

SET @strTransformedVal = ??

Question: if the goal is to arrive at @strTransformedVal as described
above, what is the best way to accomplish that...should one use the
REPLACE function?

Thank you.

[Back to original message]