Replacing with multiple words - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Replacing with multiple words

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-08-27 : 21:10:53

hi

I have a string "The quick brown fox jumps over the lazy dog" and i want to replace a few words in the string. Like "The slow red fox climbs over the sleeping dog"

I have setup a table called match with 2 fields
[pattern], [Replace] with some data.
quick slow
brown red
jumps climbs
lazy sleeping

How could i use the sql replace to achieve this? Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-28 : 02:40:36

create a UDF like this


CREATE FUNCTION ReplacePattern
(
@Str varchar(2000)
)
RETURNS varchar(2000)
AS
BEGIN
DECLARE @RetVal varchar(2000)
;With CTE
AS
(
SELECT ID,REPLACE(@str,pat,rep) AS StrVal
FROM strrep
WHERE ID=1
UNION ALL
SELECT s.ID,REPLACE(c.StrVal,s.pat,s.rep)
FROM CTE c
JOIN strrep s
ON s.ID=c.ID + 1
)

SELECT TOP 1 @RetVal=StrVal FROM CTE ORDER BY ID DESC
RETURN @RetVal
END

and use it in your code like


ECLARE @str varchar(2000)
CREATE TABLE strrep 
(
ID int identity(1,1),
pat varchar(100),
rep varchar(100)
)

INSERT strrep
Select 'quick', 'slow' UNION ALL
Select 'brown', 'red' UNION ALL
Select 'jumps', 'climbs' UNION ALL
Select 'lazy', 'sleeping'

SELECT @str='The quick brown fox jumps over the lazy dog'
SELECT dbo.ReplacePattern(@str)