Replace multiple instances in select statement

Question

SQLServer 2005 user Here is an example of a line I'm stuck on: {\ rtf1 \ ansi \ ansicpg1252 \ deff0 \ deflang1033 {\ fonttbl {\ f0 \ fnil \ fcharset0 Arial Rounded MT Bold;} {\ f1 \ fnil \ fcharset0 Arial;}} \ viewkind4 \ uc1 \ pard \ f0 \ fs54 1000 \ f1 \ fs20 \ par}

I want to replace any font name with "Times New Roman"

I can get the first with (textlong1 is this field):

Select Replace(textlong1, CASE When CharIndex(';',textlong1)> 10 Then SubString(textlong1 , Charindex('fcharset',textlong1)+10 , CharIndex(';',textlong1) - Charindex('fcharset',textlong1)-10) Else '' End , 'Times New Roman') From exampletable 

I use case statement to prevent SubString error.

Since I do not replace "fcharset", even if I go through it, it does not find the second instance (it always loops on the first).

Answer 1

If you can replace the first, just keep replacing until the first one. If you are doing an update:

 declare @done bit while @done <> 1 begin UPDATE ... if @@rowcount = 0 set done = 1 end 

Or selecting a new variable:

 declare @cur varchar(4000) declare @next varchar(4000) set @cur = 'The string to operate on' set @next = '' while @cur <> @next begin set @next = @cur select @cur = REPLACE(@next,...) end 

The final result is now saved in @cur (and @next too.)

Answer 2

If you can integrate the .NET CLR functionality (MSDN has many examples for this), you can use regex replacement and make your task very simple.

Answer 3

You can always use the delimiter procedure to break a string around instances of your font list and replace them with new roman.