[2k] Text 필드 치환하기

use tempdb
go

select pub_id, pr_info
into text_tbl
from pubs.dbo.pub_info
go

select *
from text_tbl
where pub_id = '0736'
go

--아래 스크립트는 ROW단위 Replace한다.
declare @replacestr varchar(10)
declare @deletestr varchar(10)
declare @ptr varbinary(16)
declare @len int
declare @offset int
declare @cnt int

set @deletestr = 'this'
set @replacestr = '가'
select @ptr = textptr(pr_info) from text_tbl where pub_id = '0736' --컬럼, 테이블
set @len = len(@deletestr)
set @cnt = 0

while(1=1)
begin
   select @offset = patindex('%'+@deletestr+'%', pr_info) from text_tbl where pub_id = '0736'  --컬럼, 테이블
   if @offset = 0
      begin
         break
      end
   else
      begin
         set @offset = @offset - 1
         updatetext text_tbl.pr_info @ptr @offset @len @replacestr  --테이블.컬럼
         set @cnt = @cnt + 1
      end
end
select  ' Replaced ' + convert(varchar(10), @cnt) + ' e.a.'
go

--확인
select *
from text_tbl
where pub_id = '0736'
go

--정리
drop table text_tbl
go

--추가
--datalength()로 확인시 8000이 안 넘으면 그냥 치환해서 replace하는게 좋다.
Update text_tbl
set pr_info = replace(cast (pr_info as varchar(8000)), 'this', '빵')