the solution of the database to replace text,ntext type

最新推荐文章于 2024-07-28 19:10:22 发布

axpbiledjpihry3234

最新推荐文章于 2024-07-28 19:10:22 发布

阅读量71

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/ITAres/archive/2008/02/22/1077012.html

版权

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

转载于:https://www.cnblogs.com/ITAres/archive/2008/02/22/1077012.html

axpbiledjpihry3234

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
the solution of the database to replace text,ntext type

mssql2000 solutionCode1ifexists(select1fromtempdb.dbo.sysobjectswherenamelike'#cms_extendtmp%')2DROPTABLE#cms_extendtmp3CREATETABLE#cms_extendtmp4...
复制链接

扫一扫