SQL replace all instances of word in a field

Question

Any way to do this? Replace function only replaces first instance.

Thanks.

Answer 1

Try this in a new query:

 DECLARE @Phrase varchar(1000)
 SELECT @Phrase = 'az a1 az a3 az a4 az a6'
 SELECT REPLACE(@Phrase, 'az', 'B')

This results in your expected/desired behaviour:

B a1 B a3 B a4 B a6

Answer 2

According to MSDN:

Replaces all occurrences of a specified string value with another string value.

Try this:

DECLARE @x nvarchar(50)
SET @x = 'BobbyBobbyBobby'

SET @x = replace(@x, 'Bobby', '')

PRINT '!' + @x + '!'

It will print

!!

See the MSDN documentation for more information.