replacing substring within string

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.

Re: replacing substring within string

(mirandacascade@yahoo.com) writes:
> -- 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?

As long as the job is so simple that replace() does the job, why not
use it?

I would not be surprised, though, if you find that the affair is more
complex. Keep in mind that T-SQL is not the best tool for string
manipulation. If you are on SQL 2005, you have access to the CLR and
can use C# or VB.Net in SQL Server.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx