the solution of the database to replace text,ntext type
mssql2000 solution
Code
1 if exists(select 1 from tempdb.dbo.sysobjects where name like '#cms_extendtmp%')
2 DROP TABLE #cms_extendtmp
3 CREATE TABLE #cms_extendtmp
4
5 (
6
7 ID INT IDENTITY(1,1) PRIMARY KEY,
8
9 A TEXT
10
11 )
12
13 set nocount on
14
15 DECLARE @sTableName SYSNAME
16
17 DECLARE @sFieLd SYSNAME
18
19 DECLARE @sSrc VARCHAR(20)
20
21 DECLARE @sDest VARCHAR(20)
22
23
24
25
26
27 SET @sTableName = 'cms_extend' -- 待更新的表的名称
28
29 SET @sField = 'tvalue' -- 待更新的字段的名称
30
31 SET @sSrc = '.MEGAJOY.COM' -- 要被替换的字符串（源字符串）
32
33 SET @sDest = '.joy.cn' -- 进行替换的目标字符串
34
35
36
37 DECLARE @PTRVAL BINARY(16) -- 指向当前文本字段的指针
38
39 DECLARE @VAL VARCHAR(8000) -- 当前读取的文本字段的部分
40
41 DECLARE @PREVVAL VARCHAR(8000) -- 由于源字符串跨界产生的源字符串前缀回退部分
42
43 DECLARE @VALLEN INT -- 当前读取的文本字段的长度
44
45 DECLARE @STATEMENT NVARCHAR(512) -- 进行执行sp_executesql的SQL语句部分
46
47 DECLARE @ISNTEXT BIT -- 是否NTEXT字段类型0: TEXT 1: NTEXT
48
49 DECLARE @READPTR INT -- 当前TEXT或者NTEXT字段已经读取或者处理的字符偏移量
50
51 DECLARE @READLEN INT -- 当次TEXT或者NTEXT字段读取的长度
52
53 DECLARE @SRCLEN INT -- 源字符串的长度
54
55 DECLARE @DESTLEN INT -- 目标字符串的长度
56
57 DECLARE @VALBINARY VARBINARY(8000) -- 本次读取的文本字段的BINARY表示，用来识别是否已经截断了一个DBCS字符
58
59 DECLARE @VALBINARYLEN INT -- 本次读取的文本字段的BINARY表示的长度
60
61 DECLARE @HALFDBCS INT -- 当前识别的是否DBCS的一部分
62
63
64
65
66
67 SET @SRCLEN = LEN( @sSrc )
68
69 SET @DESTLEN = LEN( @sDest )
70
71
72
73 DECLARE csrTextUpdate CURSOR LOCAL FOR
74
75 SELECT TEXTPTR(tvalue),
76
77 DATALENGTH(tvalue)
78
79 FROM cms_extend
80
81 FOR UPDATE OF tvalue
82
83
84
85 OPEN csrTextUpdate
86
87 declare @count int
88 select @count=count(*) from cms_extend
89 print '共'+cast(@count as varchar);
90 set @count =1;
91
92
93 -- xtype = 35 为TEXT xtype = 99 为NTEXT
94
95 IF EXISTS( SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('cms_extend') AND NAME = @sField AND XTYPE = 35 )
96
97 SET @ISNTEXT = 0
98
99 ELSE
100
101 SET @ISNTEXT = 1
102
103
104
105 FETCH csrTextUpdate INTO @PTRVAL, @VALLEN
106
107 WHILE @@FETCH_STATUS = 0
108
109 BEGIN
110 print '现在是第'+cast(@count as varchar);
111 set @count=@count+1;
112 IF @VALLEN > 8000
113
114 BEGIN
115
116
117
118 SET @VALLEN = CASE WHEN @ISNTEXT = 1 THEN @VALLEN / 2 ELSE @VALLEN END
119
120 SET @READPTR = 0
121
122 SET @READLEN = CASE WHEN @VALLEN > @READPTR + 2000 THEN 2000 ELSE @VALLEN - @READPTR END
123
124
125
126 TRUNCATE TABLE #cms_extendtmp
127
128
129
130 -- 分拆TEXT字段到#cms_extendtmp表
131
132 WHILE @READPTR < @VALLEN
133
134 BEGIN
135
136
137
138 SET @READLEN = CASE WHEN @VALLEN > @READPTR + 2000 THEN 2000 ELSE @VALLEN - @READPTR END
139
140 SET @STATEMENT = N'READTEXT cms_extend.tvalue @PTRVAL '
141
142 + CONVERT( NVARCHAR, @READPTR ) + ' '
143
144 + CONVERT( NVARCHAR, @READLEN )
145
146
147
148 SET @READPTR = @READPTR + @READLEN
149
150
151
152 INSERT INTO #cms_extendtmp
153
154 EXEC sp_executesql @STATEMENT,
155
156 N'@PTRVAL BINARY(16)',
157
158 @PTRVAL
159
160
161 -- 判断最后一个字符是否为中文字符，如果是，则需要进行回退操作
162
163
164
165 IF @@ROWCOUNT = 1 AND @ISNTEXT = 0
166
167 BEGIN
168
169 SELECT @VALBINARY = CONVERT( VARBINARY(8000), CONVERT( VARCHAR(8000), A ))
170
171 FROM #cms_extendtmp WHERE ID = @@IDENTITY
172
173 SET @VALBINARYLEN = DATALENGTH(@VALBINARY)
174
175
176
177 IF @VALBINARYLEN = @READLEN
178
179 BEGIN
180
181 SET @HALFDBCS = 0
182
183 END
184
185 ELSE
186
187 BEGIN
188
189 SET @HALFDBCS = 1
190
191 END
192
193
194
195 IF @HALFDBCS = 1
196
197 BEGIN
198
199 -- 回退一个字符串
200
201 UPDATE #cms_extendtmp
202
203 SET A = CONVERT( VARCHAR(8000), SUBSTRING( @VALBINARY, 1, @READLEN - 1 ) )
204
205 WHERE ID = @@IDENTITY
206
207 SET @READPTR = @READPTR - 1
208
209 END
210
211 END
212
213 END
214
215
216
217 -- 进行分批替换,如果有需要被替换的字符串跨段的情况需要进行跨段处理
218
219 DECLARE csrTmp CURSOR LOCAL FOR
220
221 SELECT A FROM #cms_extendtmp
222
223 FOR UPDATE OF A
224
225 OPEN csrTmp
226
227 FETCH csrTmp INTO @VAL
228
229
230
231 SET @PREVVAL = ''
232
233 WHILE @@FETCH_STATUS = 0
234
235 BEGIN
236
237 SET @VAL = REPLACE( @PREVVAL + @VAL, @sSrc, @sDest )
238
239
240
241 SET @VALLEN = LEN(@VAL)
242
243
244
245 SET @READPTR = CASE WHEN @SRCLEN > @VALLEN THEN @VALLEN ELSE @SRCLEN END
246
247
248
249 SET @PREVVAL = ''
250
251
252
253 WHILE @READPTR > 0
254
255 BEGIN
256
257
258
259 IF RIGHT( @VAL, @READPTR ) = LEFT( @sSrc, @READPTR )
260
261 BEGIN
262
263
264
265 -- 找到前缀，将当前的@VAL截断一部分
266
267 UPDATE #cms_extendtmp
268
269 SET [A] = LEFT( @VAL, @VALLEN - @READPTR )
270
271 WHERE CURRENT OF csrTmp
272
273
274
275 SET @PREVVAL = RIGHT( @VAL, @READPTR )
276
277 BREAK
278
279 END
280
281
282
283 SET @READPTR = @READPTR - 1
284
285 END
286
287
288
289 IF @PREVVAL = ''
290
291 BEGIN
292
293 UPDATE #cms_extendtmp
294
295 SET [A] = @VAL
296
297 WHERE CURRENT OF csrTmp
298
299 END
300
301
302
303 FETCH csrTmp INTO @VAL
304
305 END
306
307 DEALLOCATE csrTmp
308
309
310
311
312
313 -- 更新TEXT字段
314
315 UPDATETEXT cms_extend.tvalue @PTRVAL 0 NULL ''
316
317
318
319 DECLARE csrUpdateText CURSOR LOCAL FOR
320
321 SELECT A FROM #cms_extendtmp
322
323
324
325 OPEN csrUpdateText
326
327 FETCH csrUpdateText INTO @VAL
328
329 WHILE @@FETCH_STATUS = 0
330
331 BEGIN
332
333
334
335 UPDATETEXT cms_extend.tvalue @PTRVAL NULL 0 @VAL
336
337 FETCH csrUpdateText INTO @VAL
338
339 END
340
341 DEALLOCATE csrUpdateText
342
343
344
345 END
346
347 ELSE
348
349 BEGIN
350
351 -- 如果是NTEXT的类型，字符串的长度为字节长度的/2
352
353 UPDATE cms_extend
354
355 SET tvalue = REPLACE( CONVERT( VARCHAR(8000), tvalue), @sSrc, @sDest )
356
357 WHERE CURRENT OF csrTextUpdate
358
359 END
360
361
362
363 FETCH csrTextUpdate INTO @PTRVAL, @VALLEN
364
365 END
366
367
368
369 DEALLOCATE csrTextUpdate
370
371 GO
372
373
374
mssql2005+ solution
Code
1
update table set column=replace(cast(column as varchar(max),'megajoy.com','joy.cn'))
the code is very simple,so i won't intend to explain it,if you have any question , please leave word to me ..i'm sorry
Code1 if exists(select 1 from tempdb.dbo.sysobjects where name like '#cms_extendtmp%')
2 DROP TABLE #cms_extendtmp
3 CREATE TABLE #cms_extendtmp
4
5 (
6
7 ID INT IDENTITY(1,1) PRIMARY KEY,
8
9 A TEXT
10
11 )
12
13 set nocount on
14
15 DECLARE @sTableName SYSNAME
16
17 DECLARE @sFieLd SYSNAME
18
19 DECLARE @sSrc VARCHAR(20)
20
21 DECLARE @sDest VARCHAR(20)
22
23
24
25
26
27 SET @sTableName = 'cms_extend' -- 待更新的表的名称
28
29 SET @sField = 'tvalue' -- 待更新的字段的名称
30
31 SET @sSrc = '.MEGAJOY.COM' -- 要被替换的字符串（源字符串）
32
33 SET @sDest = '.joy.cn' -- 进行替换的目标字符串
34
35
36
37 DECLARE @PTRVAL BINARY(16) -- 指向当前文本字段的指针
38
39 DECLARE @VAL VARCHAR(8000) -- 当前读取的文本字段的部分
40
41 DECLARE @PREVVAL VARCHAR(8000) -- 由于源字符串跨界产生的源字符串前缀回退部分
42
43 DECLARE @VALLEN INT -- 当前读取的文本字段的长度
44
45 DECLARE @STATEMENT NVARCHAR(512) -- 进行执行sp_executesql的SQL语句部分
46
47 DECLARE @ISNTEXT BIT -- 是否NTEXT字段类型0: TEXT 1: NTEXT
48
49 DECLARE @READPTR INT -- 当前TEXT或者NTEXT字段已经读取或者处理的字符偏移量
50
51 DECLARE @READLEN INT -- 当次TEXT或者NTEXT字段读取的长度
52
53 DECLARE @SRCLEN INT -- 源字符串的长度
54
55 DECLARE @DESTLEN INT -- 目标字符串的长度
56
57 DECLARE @VALBINARY VARBINARY(8000) -- 本次读取的文本字段的BINARY表示，用来识别是否已经截断了一个DBCS字符
58
59 DECLARE @VALBINARYLEN INT -- 本次读取的文本字段的BINARY表示的长度
60
61 DECLARE @HALFDBCS INT -- 当前识别的是否DBCS的一部分
62
63
64
65
66
67 SET @SRCLEN = LEN( @sSrc )
68
69 SET @DESTLEN = LEN( @sDest )
70
71
72
73 DECLARE csrTextUpdate CURSOR LOCAL FOR
74
75 SELECT TEXTPTR(tvalue),
76
77 DATALENGTH(tvalue)
78
79 FROM cms_extend
80
81 FOR UPDATE OF tvalue
82
83
84
85 OPEN csrTextUpdate
86
87 declare @count int
88 select @count=count(*) from cms_extend
89 print '共'+cast(@count as varchar);
90 set @count =1;
91
92
93 -- xtype = 35 为TEXT xtype = 99 为NTEXT
94
95 IF EXISTS( SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('cms_extend') AND NAME = @sField AND XTYPE = 35 )
96
97 SET @ISNTEXT = 0
98
99 ELSE
100
101 SET @ISNTEXT = 1
102
103
104
105 FETCH csrTextUpdate INTO @PTRVAL, @VALLEN
106
107 WHILE @@FETCH_STATUS = 0
108
109 BEGIN
110 print '现在是第'+cast(@count as varchar);
111 set @count=@count+1;
112 IF @VALLEN > 8000
113
114 BEGIN
115
116
117
118 SET @VALLEN = CASE WHEN @ISNTEXT = 1 THEN @VALLEN / 2 ELSE @VALLEN END
119
120 SET @READPTR = 0
121
122 SET @READLEN = CASE WHEN @VALLEN > @READPTR + 2000 THEN 2000 ELSE @VALLEN - @READPTR END
123
124
125
126 TRUNCATE TABLE #cms_extendtmp
127
128
129
130 -- 分拆TEXT字段到#cms_extendtmp表
131
132 WHILE @READPTR < @VALLEN
133
134 BEGIN
135
136
137
138 SET @READLEN = CASE WHEN @VALLEN > @READPTR + 2000 THEN 2000 ELSE @VALLEN - @READPTR END
139
140 SET @STATEMENT = N'READTEXT cms_extend.tvalue @PTRVAL '
141
142 + CONVERT( NVARCHAR, @READPTR ) + ' '
143
144 + CONVERT( NVARCHAR, @READLEN )
145
146
147
148 SET @READPTR = @READPTR + @READLEN
149
150
151
152 INSERT INTO #cms_extendtmp
153
154 EXEC sp_executesql @STATEMENT,
155
156 N'@PTRVAL BINARY(16)',
157
158 @PTRVAL
159
160
161 -- 判断最后一个字符是否为中文字符，如果是，则需要进行回退操作
162
163
164
165 IF @@ROWCOUNT = 1 AND @ISNTEXT = 0
166
167 BEGIN
168
169 SELECT @VALBINARY = CONVERT( VARBINARY(8000), CONVERT( VARCHAR(8000), A ))
170
171 FROM #cms_extendtmp WHERE ID = @@IDENTITY
172
173 SET @VALBINARYLEN = DATALENGTH(@VALBINARY)
174
175
176
177 IF @VALBINARYLEN = @READLEN
178
179 BEGIN
180
181 SET @HALFDBCS = 0
182
183 END
184
185 ELSE
186
187 BEGIN
188
189 SET @HALFDBCS = 1
190
191 END
192
193
194
195 IF @HALFDBCS = 1
196
197 BEGIN
198
199 -- 回退一个字符串
200
201 UPDATE #cms_extendtmp
202
203 SET A = CONVERT( VARCHAR(8000), SUBSTRING( @VALBINARY, 1, @READLEN - 1 ) )
204
205 WHERE ID = @@IDENTITY
206
207 SET @READPTR = @READPTR - 1
208
209 END
210
211 END
212
213 END
214
215
216
217 -- 进行分批替换,如果有需要被替换的字符串跨段的情况需要进行跨段处理
218
219 DECLARE csrTmp CURSOR LOCAL FOR
220
221 SELECT A FROM #cms_extendtmp
222
223 FOR UPDATE OF A
224
225 OPEN csrTmp
226
227 FETCH csrTmp INTO @VAL
228
229
230
231 SET @PREVVAL = ''
232
233 WHILE @@FETCH_STATUS = 0
234
235 BEGIN
236
237 SET @VAL = REPLACE( @PREVVAL + @VAL, @sSrc, @sDest )
238
239
240
241 SET @VALLEN = LEN(@VAL)
242
243
244
245 SET @READPTR = CASE WHEN @SRCLEN > @VALLEN THEN @VALLEN ELSE @SRCLEN END
246
247
248
249 SET @PREVVAL = ''
250
251
252
253 WHILE @READPTR > 0
254
255 BEGIN
256
257
258
259 IF RIGHT( @VAL, @READPTR ) = LEFT( @sSrc, @READPTR )
260
261 BEGIN
262
263
264
265 -- 找到前缀，将当前的@VAL截断一部分
266
267 UPDATE #cms_extendtmp
268
269 SET [A] = LEFT( @VAL, @VALLEN - @READPTR )
270
271 WHERE CURRENT OF csrTmp
272
273
274
275 SET @PREVVAL = RIGHT( @VAL, @READPTR )
276
277 BREAK
278
279 END
280
281
282
283 SET @READPTR = @READPTR - 1
284
285 END
286
287
288
289 IF @PREVVAL = ''
290
291 BEGIN
292
293 UPDATE #cms_extendtmp
294
295 SET [A] = @VAL
296
297 WHERE CURRENT OF csrTmp
298
299 END
300
301
302
303 FETCH csrTmp INTO @VAL
304
305 END
306
307 DEALLOCATE csrTmp
308
309
310
311
312
313 -- 更新TEXT字段
314
315 UPDATETEXT cms_extend.tvalue @PTRVAL 0 NULL ''
316
317
318
319 DECLARE csrUpdateText CURSOR LOCAL FOR
320
321 SELECT A FROM #cms_extendtmp
322
323
324
325 OPEN csrUpdateText
326
327 FETCH csrUpdateText INTO @VAL
328
329 WHILE @@FETCH_STATUS = 0
330
331 BEGIN
332
333
334
335 UPDATETEXT cms_extend.tvalue @PTRVAL NULL 0 @VAL
336
337 FETCH csrUpdateText INTO @VAL
338
339 END
340
341 DEALLOCATE csrUpdateText
342
343
344
345 END
346
347 ELSE
348
349 BEGIN
350
351 -- 如果是NTEXT的类型，字符串的长度为字节长度的/2
352
353 UPDATE cms_extend
354
355 SET tvalue = REPLACE( CONVERT( VARCHAR(8000), tvalue), @sSrc, @sDest )
356
357 WHERE CURRENT OF csrTextUpdate
358
359 END
360
361
362
363 FETCH csrTextUpdate INTO @PTRVAL, @VALLEN
364
365 END
366
367
368
369 DEALLOCATE csrTextUpdate
370
371 GO
372
373
374
mssql2005+ solution
Code1
update table set column=replace(cast(column as varchar(max),'megajoy.com','joy.cn'))the code is very simple,so i won't intend to explain it,if you have any question , please leave word to me ..i'm sorry
