Форум пользователей MySQL

declare @prodId int
declare getproduct cursor for select Id from Product where IsActive = 1 and Description like '%<img src%'
declare @imgString varchar(200)
declare @endPos int
declare @desc varchar(max)
declare @position int
declare @outputDesc varchar(max)

    open getproduct
fetch next from getproduct into @prodId

while @@FETCH_STATUS = 0
begin
            -- get product Id
    select @desc = Description from Product where Id = @prodId

  --gets the index of the first instance of <img
    select @position = CHARINDEX('<img', @desc)
    while @position < len(@desc)
    begin
        --this assumes that we are not at the end of the description field
        if (SUBSTRING(@desc, @position, 4) = '<img')
        begin

                select @endPos = charIndex('>', substring(@desc, @position, 200))
                select @imgString = substring(@desc, @position, @endPos)



                    insert into dbo.ProductImage(ProductId, ImageUrl, DisplayName, IsPrimaryImage)
                    select @prodId, @imgString, DisplayName, 0
                    from Product where Id = @prodId
                                 and not exists (select ImageUrl from ProductImage where ProductId = @prodId and ImageUrl = @imgString)


                select @outputDesc = REPLACE(@desc, @imgString, '')
                select @outputDesc = Replace(@outputDesc, '</img>', '')

                select @position = @endPos  

        end
        else
        begin
                           -- if we have reached here, there are no more instances of <img
            -- set @position to end of description field to prevent continuous looping
            select @position = len(@desc)
        end




    end

    select @outputDesc

    update Product
    set Description = @outputDesc
    where Id = @prodId

    select Description from Product where Id = @prodId



fetch next from getproduct into @prodId
end

close getproduct
deallocate getproduct