34,587
社区成员
发帖
与我相关
我的任务
分享ntext字段字符串替换.Help~~
急求.!
很久弄过代码了..茫然Ing...
网上找的要不是只能替换第一个个.要不是替换不了.@ @
例如:
A residential bazaar[GTS]conduct[GTS]doesn't chatter[GTS]
"[GTS]"替换成"[TVB]"吧.
字数可能在8000以上..
谢谢各位大大了先..
很久弄过代码了..茫然Ing...
网上找的要不是只能替换第一个个.要不是替换不了.@ @
例如:
A residential bazaar[GTS]conduct[GTS]doesn't chatter[GTS]
"[GTS]"替换成"[TVB]"吧.
字数可能在8000以上..
谢谢各位大大了先..
...全文
请发表友善的回复…
发表回复
abuying 2011-02-27
- 打赏
- 举报
不什么不使用全文索引呢！
rucypli 2011-02-26
- 打赏
- 举报
先把ntext字段转化成nvarchar(max)
然后replace
然后replace
Linares 2011-02-26
- 打赏
- 举报
--IF @pos>=0
while @pos >=0
begin
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(content) --SELECT @ptrval = TEXTPTR(字段名)
from ReplaceTable --表名
where id = @i --条件 HID是Hotel表的标示字段
UPDATETEXT ReplaceTable.content @ptrval @pos @len @str --UPDATETEXT 表名.字段名 @ptrval @pos @len @str
select @pos=patINDEX('%'+@des+'%',content) - 1 --select @pos=patINDEX('%'+@des+'%',字段名) - 1
from ReplaceTable --表名
where id = @i --条件 ID是ReplaceTable表的标示字段
set @count=@count+1
end 
SamuDra 2011-02-26
- 打赏
- 举报
但是count得出来却是更改过的.1W+
SamuDra 2011-02-26
- 打赏
- 举报
declare @i int
declare @pos int
declare @len int
declare @str nvarchar(4000)
declare @des nvarchar(4000)
declare @count int
set @des ='[GTS]' --要替换的字符
set @len=len(@des)
set @str= '[TVB]' --要替换成的值
set @count=0 --统计次数.
set @i=2
while @i<=10796 --最大ID 或者随便指定一个记录总数
begin
--SELECT HID,HNAME FROM Hotel where Hid = @i
select @pos=patINDEX('%'+@des+'%',content) - 1 --select @pos=patINDEX('%'+@des+'%',字段名) - 1
from ReplaceTable --表名
where id = @i --条件 ID是ReplaceTable表的标示字段
IF @pos>=0
begin
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(content) --SELECT @ptrval = TEXTPTR(字段名)
from ReplaceTable --表名
where id = @i --条件 HID是Hotel表的标示字段
UPDATETEXT ReplaceTable.content @ptrval @pos @len @str --UPDATETEXT 表名.字段名 @ptrval @pos @len @str
set @count=@count+1
end
-- ELSE
-- break;
set @i=@i+1
end
select @count
只能更新第一个..
SamuDra 2011-02-26
- 打赏
- 举报
[Quote=引用 5 楼 ssp2009 的回复:]
SQL code
declare @str varchar(max)
set @str='A residential bazaar[GTS]conduct[GTS]doesn''t chatter[GTS]to be big and aldermanic[GTS]abnegation[GTS]to coupling[GTS]a cell[GTS]area, as its atoning[GT……
[/Quote]
是额.可以的.但是更新ntext类型的字段好像就没作用了.= =
我也不想伸手主义.
但是我也不是专门代码的,只是工作急需.
所以如果可以的话.能给代码嘛.?应该是updatetext..
但是我在网上找到只能更新第一个.@ @
SQL code
declare @str varchar(max)
set @str='A residential bazaar[GTS]conduct[GTS]doesn''t chatter[GTS]to be big and aldermanic[GTS]abnegation[GTS]to coupling[GTS]a cell[GTS]area, as its atoning[GT……
[/Quote]
是额.可以的.但是更新ntext类型的字段好像就没作用了.= =
我也不想伸手主义.
但是我也不是专门代码的,只是工作急需.
所以如果可以的话.能给代码嘛.?应该是updatetext..
但是我在网上找到只能更新第一个.@ @
AcHerat 元老 2011-02-26
- 打赏
- 举报
写游标循环处理吧！写省的有人继续忽悠！
快溜 2011-02-26
- 打赏
- 举报
得写个循环处理了。
Linares 2011-02-26
- 打赏
- 举报
05/08
replace(convert(nvarchar(max),colname), '[GTS]', '[TVB]')
00
自己看联机textprt/updatetext，好过在这里听人忽悠。
replace(convert(nvarchar(max),colname), '[GTS]', '[TVB]')
00
自己看联机textprt/updatetext，好过在这里听人忽悠。
快溜 2011-02-26
- 打赏
- 举报
declare @str varchar(max)
set @str='A residential bazaar[GTS]conduct[GTS]doesn''t chatter[GTS]to be big and aldermanic[GTS]abnegation[GTS]to coupling[GTS]a cell[GTS]area, as its atoning[GTS]abstract[GTS]be a analyze[GTS]car or champ[GTS]two to three vehicles. On the advancing[GTS]hand, a celebration[GTS]bazaar[GTS]has awkward[GTS]algid[GTS]requirements as they chatter[GTS]to augur[GTS]anathema[GTS]and canker[GTS]openings. The hard[GTS]is to use advisers[GTS]doors for celebration[GTS]garagenda[GTS]usualluvial[GTS]angle[GTS]doors. It abstract[GTS]not arched[GTS]a clover[GTS]credible[GTS]outabode[GTS]to the bazaar[GTS]but it is stronger, amazement[GTS]and connected[GTS]readdition[GTS]than the asthmatic[GTS]ones that are usualluvial[GTS]accidentally[GTS]in residential garages. They absorption[GTS]advertise[GTS]action[GTS]abject[GTS]than the asthmatic[GTS]ones as a business requires ballista[GTS]afterwards[GTS]thefts and bark[GTS]ins. agitation[GTS]from that, a huge adadvancement[GTS]is that angle[GTS]doors are atone[GTS]cheaper than the angrily[GTS]ones. However, this would accessory[GTS]bowl[GTS]depend abstract[GTS]the bush[GTS]and the adulterated[GTS]you readvisedly[GTS]in your customized angle[GTS]door. By adulterated[GTS]it is meant thatare you opting for an abating[GTS]conduct[GTS]to cut on your admired[GTS]acrimony[GTS]and actor[GTS]press[GTS]levels, an overaperture[GTS]door, a horizontalluvial[GTS]calendar[GTS]conduct[GTS]or any advancing[GTS]of the anatomic[GTS]choices available. As a conduct[GTS]gives the beat[GTS]ambiguous[GTS]of the abject[GTS]of a location, therefore, it is top[GTS]important to use a conduct[GTS]that can chaplet[GTS]abate[GTS]canker[GTS]pressures, alive[GTS]hazards, clarify[GTS]hazards, countdown[GTS]and tear, attestant[GTS]and even bullets- in case of top akin[GTS]garages! It should circling[GTS]of dangary[GTS]by its abandoned[GTS]outabode[GTS]and award[GTS]presence. You could accomplishment[GTS]even ardent[GTS]how you admeasurement[GTS]your conduct[GTS]to operate. For examphitheater[GTS]you could set time the conduct[GTS]should arrange[GTS]to elderliness[GTS]and close- whether you admeasurement[GTS]that to be realluvial[GTS]speedy, biased[GTS]or slow. It could be bother[GTS]absolutist[GTS]or partly administer[GTS]or accountable[GTS]as you admeasurement[GTS]it! The custom-built[GTS]anarchic[GTS]a celebration[GTS]bazaar[GTS]would affably[GTS]readvisedly[GTS]is decollate[GTS]boyish[GTS]aroma[GTS]abonds[GTS]how active[GTS]the conduct[GTS]would supacclamation[GTS]it in the future. Therefore, considerately[GTS]this accurate[GTS]for yourbury[GTS]able-bodied[GTS]and go for the conduct[GTS]that stays with you the longest and that''s the absolutely[GTS]of a angle[GTS]one! A asthmatic[GTS]or a babe[GTS]conduct[GTS]would be connected[GTS]accumulation[GTS]to countdown[GTS]and analytical[GTS]and analysis[GTS]due to adavidity[GTS]accounting[GTS]conditions. So be bulk[GTS]and go for the one you could tattestant[GTS]in the active[GTS]term!Aladmirable[GTS]the asthmatic[GTS]ones bank[GTS]clover[GTS]eye-casket[GTS]and abstract[GTS]even fabricated[GTS]charity[GTS]and arced[GTS]stop by, but at the end of the day what affectation[GTS]is the ballista[GTS]of your garage. You would bowl[GTS]not admeasurement[GTS]to let go of an abundant[GTS]aimless[GTS]abscess[GTS]in your bazaar[GTS]for reagitable[GTS]or advancing[GTS]affection[GTS]accountable[GTS]for an addictive[GTS]action[GTS]a day! The answer[GTS]you abhor[GTS]in your arced[GTS]for your bazaar[GTS]is the bill[GTS]accretion[GTS]ability[GTS]of your business!'
select replace(@str,'[GTS]','[TVB]')
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A residential bazaar[TVB]conduct[TVB]doesn't chatter[TVB]to be big and aldermanic[TVB]abnegation[TVB]to coupling[TVB]a cell[TVB]area, as its atoning[TVB]abstract[TVB]be a analyze[TVB]car or champ[TVB]two to three vehicles. On the advancing[TVB]hand, a cele
(1 行受影响)
SamuDra 2011-02-26
- 打赏
- 举报
[Quote=引用 1 楼 acherat 的回复:]
SQL code
select replace('A residential bazaar[GTS]conduct[GTS]doesn''''t chatter[GTS]','[GTS]','[TVB]')
/*
--------------------------------------------------------------------------------------……
[/Quote]
8000以上字数能用嘛.?ntext.
SQL code
select replace('A residential bazaar[GTS]conduct[GTS]doesn''''t chatter[GTS]','[GTS]','[TVB]')
/*
--------------------------------------------------------------------------------------……
[/Quote]
8000以上字数能用嘛.?ntext.
A residential bazaar[GTS]conduct[GTS]doesn't chatter[GTS]to be big and aldermanic[GTS]abnegation[GTS]to coupling[GTS]a cell[GTS]area, as its atoning[GTS]abstract[GTS]be a analyze[GTS]car or champ[GTS]two to three vehicles. On the advancing[GTS]hand, a celebration[GTS]bazaar[GTS]has awkward[GTS]algid[GTS]requirements as they chatter[GTS]to augur[GTS]anathema[GTS]and canker[GTS]openings. The hard[GTS]is to use advisers[GTS]doors for celebration[GTS]garagenda[GTS]usualluvial[GTS]angle[GTS]doors. It abstract[GTS]not arched[GTS]a clover[GTS]credible[GTS]outabode[GTS]to the bazaar[GTS]but it is stronger, amazement[GTS]and connected[GTS]readdition[GTS]than the asthmatic[GTS]ones that are usualluvial[GTS]accidentally[GTS]in residential garages. They absorption[GTS]advertise[GTS]action[GTS]abject[GTS]than the asthmatic[GTS]ones as a business requires ballista[GTS]afterwards[GTS]thefts and bark[GTS]ins. agitation[GTS]from that, a huge adadvancement[GTS]is that angle[GTS]doors are atone[GTS]cheaper than the angrily[GTS]ones. However, this would accessory[GTS]bowl[GTS]depend abstract[GTS]the bush[GTS]and the adulterated[GTS]you readvisedly[GTS]in your customized angle[GTS]door. By adulterated[GTS]it is meant thatare you opting for an abating[GTS]conduct[GTS]to cut on your admired[GTS]acrimony[GTS]and actor[GTS]press[GTS]levels, an overaperture[GTS]door, a horizontalluvial[GTS]calendar[GTS]conduct[GTS]or any advancing[GTS]of the anatomic[GTS]choices available. As a conduct[GTS]gives the beat[GTS]ambiguous[GTS]of the abject[GTS]of a location, therefore, it is top[GTS]important to use a conduct[GTS]that can chaplet[GTS]abate[GTS]canker[GTS]pressures, alive[GTS]hazards, clarify[GTS]hazards, countdown[GTS]and tear, attestant[GTS]and even bullets- in case of top akin[GTS]garages! It should circling[GTS]of dangary[GTS]by its abandoned[GTS]outabode[GTS]and award[GTS]presence. You could accomplishment[GTS]even ardent[GTS]how you admeasurement[GTS]your conduct[GTS]to operate. For examphitheater[GTS]you could set time the conduct[GTS]should arrange[GTS]to elderliness[GTS]and close- whether you admeasurement[GTS]that to be realluvial[GTS]speedy, biased[GTS]or slow. It could be bother[GTS]absolutist[GTS]or partly administer[GTS]or accountable[GTS]as you admeasurement[GTS]it! The custom-built[GTS]anarchic[GTS]a celebration[GTS]bazaar[GTS]would affably[GTS]readvisedly[GTS]is decollate[GTS]boyish[GTS]aroma[GTS]abonds[GTS]how active[GTS]the conduct[GTS]would supacclamation[GTS]it in the future. Therefore, considerately[GTS]this accurate[GTS]for yourbury[GTS]able-bodied[GTS]and go for the conduct[GTS]that stays with you the longest and that's the absolutely[GTS]of a angle[GTS]one! A asthmatic[GTS]or a babe[GTS]conduct[GTS]would be connected[GTS]accumulation[GTS]to countdown[GTS]and analytical[GTS]and analysis[GTS]due to adavidity[GTS]accounting[GTS]conditions. So be bulk[GTS]and go for the one you could tattestant[GTS]in the active[GTS]term!Aladmirable[GTS]the asthmatic[GTS]ones bank[GTS]clover[GTS]eye-casket[GTS]and abstract[GTS]even fabricated[GTS]charity[GTS]and arced[GTS]stop by, but at the end of the day what affectation[GTS]is the ballista[GTS]of your garage. You would bowl[GTS]not admeasurement[GTS]to let go of an abundant[GTS]aimless[GTS]abscess[GTS]in your bazaar[GTS]for reagitable[GTS]or advancing[GTS]affection[GTS]accountable[GTS]for an addictive[GTS]action[GTS]a day! The answer[GTS]you abhor[GTS]in your arced[GTS]for your bazaar[GTS]is the bill[GTS]accretion[GTS]ability[GTS]of your business!
快溜 2011-02-26
- 打赏
- 举报
declare @str varchar(1000)
set @str='A residential bazaar[GTS]conduct[GTS]doesn''t chatter[GTS]'
select replace(@str,'[GTS]','[TVB]')
/*
---------------------------------------------------------
A residential bazaar[TVB]conduct[TVB]doesn't chatter[TVB]
AcHerat 元老 2011-02-26
- 打赏
- 举报
select replace('A residential bazaar[GTS]conduct[GTS]doesn''t chatter[GTS]','[GTS]','[TVB]')
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A residential bazaar[TVB]conduct[TVB]doesn't chatter[TVB]
(1 行受影响)
AcHerat 元老 2011-02-26
- 打赏
- 举报
select replace('A residential bazaar[GTS]conduct[GTS]doesn''''t chatter[GTS]','[GTS]','[TVB]')
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A residential bazaar[TVB]conduct[TVB]doesn''t chatter[TVB]
(1 行受影响)
Linares 2011-02-26
- 打赏
- 举报
[Quote=引用 23 楼 samudra 的回复:]
在网上找到个资料.http://www.cnblogs.com/zhwily/archive/2009/12/03/1616340.html
就干脆把ntext转换成nvarchar(max).
菜鸟.感觉写的不好.请问要怎么优化呢?
[/Quote]
优化个鸟，你能转换为nvarchar(max)，参考6楼。
在网上找到个资料.http://www.cnblogs.com/zhwily/archive/2009/12/03/1616340.html
就干脆把ntext转换成nvarchar(max).
菜鸟.感觉写的不好.请问要怎么优化呢?
[/Quote]
优化个鸟，你能转换为nvarchar(max)，参考6楼。
SamuDra 2011-02-26
- 打赏
- 举报
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[Proc_WordsReplace] Script Date: 02/26/2011 20:16:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Proc_WordsReplace]
-- Add the parameters for the stored procedure here
@count int, --初始Id
@num int, --最大值,和Id对应
@words nvarchar(max), --需要被替换的值
@repWords nvarchar(max) --替换成的值
AS
declare @c int
set @c=0
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
WHILE @count <= @num
BEGIN
if exists(SELECT id FROM replacetable where id = @count)
begin
update replacetable set content=
(select replace((select content from replacetable where id=@count),@words,@repWords))
where id=@count
set @c = @c + 1
end
set @count = @count + 1
END
END
select @c
在网上找到个资料.http://www.cnblogs.com/zhwily/archive/2009/12/03/1616340.html
就干脆把ntext转换成nvarchar(max).
菜鸟.感觉写的不好.请问要怎么优化呢?
Shawn 2011-02-26
- 打赏
- 举报
用正则吧,想怎么替换就怎么替换.
--Declare Function
IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL
DROP FUNCTION dbo.RegexReplace
GO
CREATE FUNCTION dbo.RegexReplace
(
@string VARCHAR(MAX), --被替换的字符串
@pattern VARCHAR(255), --替换模板
@replacestr VARCHAR(255), --替换后的字符串
@IgnoreCase INT = 0 --0区分大小写 1不区分大小写
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @objRegex INT, @retstr VARCHAR(8000)
--创建对象
EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT
--设置属性
EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern
EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase
EXEC sp_OASetProperty @objRegex, 'Global', 1
--执行
EXEC sp_OAMethod @objRegex, 'Replace', @retstr OUT, @string, @replacestr
--释放
EXECUTE sp_OADestroy @objRegex
RETURN @retstr
END
GO
--Reconfigure
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
--Test
CREATE TABLE #
(
memo NTEXT
)
INSERT #
SELECT 'A residential bazaar[GTS]conduct[GTS]doesn''t chatter[GTS]'
--Test
SELECT memo FROM #
UNION ALL
SELECT dbo.RegexReplace(memo, '[[]GTS[]]', '[TVB]', 1) FROM #
Linares 2011-02-26
- 打赏
- 举报
SamuDra 2011-02-26
- 打赏
- 举报
[Quote=引用 16 楼 linares 的回复:]
set @des ='[GTS]'
@pos=patINDEX('%'+@des+'%',content)
----
@pos=patINDEX('%[GTS]%',content)
死循环，第一行记录应该已经被替换为
[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[TVB]
N个左中括号
这条数据没办……
[/Quote]
那为什么后面的数据都不替换呢..循环问题.?不懂.@ @
set @des ='[GTS]'
@pos=patINDEX('%'+@des+'%',content)
----
@pos=patINDEX('%[GTS]%',content)
死循环，第一行记录应该已经被替换为
[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[TVB]
N个左中括号
这条数据没办……
[/Quote]
那为什么后面的数据都不替换呢..循环问题.?不懂.@ @
SamuDra 2011-02-26
- 打赏
- 举报
[Quote=引用 17 楼 linares 的回复:]
应该说你整个表的数据都废了
未用我循环之前所有的第一个 ...[GTS]... 都被替换为 ...[[TVB]..
[/Quote]
没事呢.我单独建的表.这是测试数据.
应该说你整个表的数据都废了
未用我循环之前所有的第一个 ...[GTS]... 都被替换为 ...[[TVB]..
[/Quote]
没事呢.我单独建的表.这是测试数据.
加载更多回复（4）
