Replace multiple instances in select statement

Question

User SQLServer 2005 Here is an example of string I'm stuck with: {\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 one with (textlong1 is the 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'm using the case statement to prevent the SubString from error.

Since I am not replacing 'fcharset', even if I loop through, it is not finding the second instance (always gets stuck on the first).

Answer 1

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

Answer 2

If you can integrate the .NET CLR functionality (MSDN has many examples for doing that), you could use a regex replace and make your task very simple.

Answer 3

If you can replace the first one, just keep replacing till there is no first one. If you're doing an update:

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

Or selecting into 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 stored in @cur (and @next too.)