set nocount on

USE sjcu
GO

EXEC sp_dboption 'sjcu', 'select into/bulkcopy', 'true'
GO

DECLARE @ptrval BINARY(16)
declare @pidx INT
declare @Loopbdseq INT
declare @SearchWord NVARCHAR(100)
declare @TargetWord NVARCHAR(100)
declare @SearchLen int

create table #tbl
(
  bdseq int,
  content ntext
)

set @SearchWord = char(10)

set @TargetWord ='<br>'
--select @SearchLen = datalength(@SearchWord)
select @SearchLen = len(@SearchWord)

DECLARE myText CURSOR FOR

SELECT bdseq
FROM pt_journalism
--where bdseq in (65297)
OPEN myText

FETCH NEXT FROM myText INTO @Loopbdseq

declare @iCnt int;
set @iCnt = 0;

WHILE (@@FETCH_STATUS = 0)
BEGIN
 DELETE FROM #tbl

 INSERT INTO #tbl

 SELECT bdseq,content FROM pt_journalism WHERE bdseq = @Loopbdseq
 SELECT @ptrval = TEXTPTR(content)  FROM #tbl

 SET @pidx = 1;
 SET @iCnt = @iCnt + 1;
 PRINT @iCnt

 WHILE (NOT @pidx = 0)
 BEGIN
  SELECT @pidx = PATINDEX('%'+@SearchWord+'%',content) FROM #tbl

  IF (NOT @pidx = 0)
  BEGIN
   SET @pidx = @pidx - 1
   UPDATETEXT #tbl.content @ptrval @pidx @SearchLen  @TargetWord
   SET @pidx = @pidx +1
  END
 END

 UPDATE pt_journalism SET
 content = B.content
 FROM pt_journalism INNER JOIN #tbl AS B
 ON pt_journalism.bdseq = B.bdseq
 WHERE pt_journalism.bdseq = @Loopbdseq

 FETCH NEXT FROM myText INTO @Loopbdseq
END

CLOSE myText
DEALLOCATE myText
DROP TABLE #tbl
GO

EXEC sp_dboption 'sjcu', 'select into/bulkcopy', 'false'
GO
SET NOCOUNT OFF

원본 출처 : http://sqler.pe.kr/web_board/view_list.asp?id=634&read=1615&pagec=5&gotopage=5&block=0&part=MyBoard7&tip=