May 18, 2007 at 12:42 pm
Hi,
I'm brasilian and not speak english, but a need a help. In my SQL Server I create a Table named tbl_Geral, and I need to change a content of fileld type text.
But this T-SQL, change only one word, and I neer to change all of then iqual words.
Please help me
Declare @Var01 Binary(16)
Declare @Posicao Int
Declare @Alterar Int
Select @Posicao = PATINDEX('%Estados Unidos%', geral_texto),
@Var01 = textptr(geral_texto),
@Alterar = len('Estados Unidos')
From tbl_geral
Where PATINDEX('%Estados Unidos%', geral_texto) >0
UPDATETEXT tbl_geral.geral_texto @Var01 @Posicao @Alterar '<a href=''#''> Estados Unidos </a>'
Select PATINDEX('%Estados Unidos%', geral_texto)
From tbl_geral
Where PATINDEX('%Estados Unidos%', geral_texto) > 0 And
PATINDEX('%<a href=''#''> Estados Unidos </a>%', geral_texto) >= 0
Select Replace(convert(Varchar(8000),geral_texto) COLLATE Latin1_General_BIN, 'Estados Unidos' ,'<a href=''#''> Estados Unidos </a>')
From tbl_geral Where DATALENGTH(geral_texto) >= 8000
May 18, 2007 at 2:00 pm
--See if you can use the following example code to accomplish what you want
--If the "replace" command doesn't work in your circumstances then substitue
--your UPDATETEXT command INSIDE the loop. Your main problem was your paternindex
--and pointer to text were not being initialized correctly (unless you only had
--one row in the table).
if object_id('test_') is not null drop table test_
create table test_ (col1_ int identity, col2_ text)
insert into test_ (col2_) values ('This is test1')
insert into test_ (col2_) values ('This is test2')
insert into test_ (col2_) values ('This is Value3')
begin
declare @col1_ int
declare @col2_ varchar(8000)
declare cur1_ cursor for
select col1_, col2_ from test_
open cur1_
fetch next from cur1_ into @col1_, @col2_
while @@fetch_status = 0
begin
--print @col2_
update test_
set col2_ = replace(@col2_,'test','<a href=''#''> Estados Unidos </a>')
where col1_ = @col1_
fetch next from cur1_ into @col1_, @col2_
end
CLOSE cur1_
DEALLOCATE cur1_
select * from test_
end
--James.
May 18, 2007 at 3:55 pm
Very Good, it's work.
Only one question, I have two tables named myWords (its a kind of dictionary) and other named myText,
I need to use the words in myWords table for change the content to myText. How can I do this. Because the tag <a> must be <a href="test.asp?idWord=1">USA</a>
Ex. word (idWord int, txtWord nVarchar)
Values
id txtWord
1 USA
2 Brasil
, and so on.
Please,
May 21, 2007 at 7:41 am
Not sure I'm following exactly what you want to do, but it sounds like you could still use the code I posted earlier. Inside the While loop, just before the UPDATE statement add code to find the "word" you want in your words table (select it into a program variable) and then use the "word" you retrieved in the final update statement.
James.
May 23, 2007 at 8:34 am
This is the new code, it's works but, the type of "geral_texto" field is a text, and the result is smaller then the original. How I change this T-SQL for use UPDATETEXT?
tks,
--Declarando variáveis
DECLARE @idGlossario int
DECLARE @strVocabulo nvarchar(150)
DECLARE CurItens cursor for --Nome do cursor
SELECT id_glossario, vocabulo FROM tbl_Glossario
--Abrindo cursor
OPEN CurItens
--Atribuindo valores do select nas variáveis
FETCH NEXT FROM CurItens INTO @idGlossario, @strVocabulo
--Iniciando laço
WHILE @@FETCH_STATUS = 0
Begin
begin
--Delcarando variáveis
declare @col1_ int
declare @col2_ varchar(8000)
declare cur1_ cursor for --Nome do cursor
select id_geral, geral_texto from tbl_geral
--Abrindo cursor
open cur1_
--Atribuindo valores do select nas variáveis
fetch next from cur1_ into @col1_, @col2_
--Iniciando laço
while @@fetch_status = 0
begin
--print @col2_
update tbl_geral
set geral_texto = replace(@col2_,'' + @strVocabulo + '', '<a href=''' + str(@idGlossario) + '''>' + @strVocabulo + '</a>')
where id_geral = @col1_
fetch next from cur1_ into @col1_, @col2_
end
CLOSE cur1_
DEALLOCATE cur1_
select id_geral,geral_texto from tbl_geral
end
FETCH NEXT FROM CurItens INTO @idGlossario, @strVocabulo
end
--Fechando e desalocando cursor
CLOSE CurItens
DEALLOCATE CurItens
May 23, 2007 at 9:40 am
Ok, I readjusted my original code to use UPDATETEXT you should be able to take it from there.
While I don't understand all your requirements (obviously) this seems like a "strange" way to accomplish what it appears you want to do. How big is your geral_texto table? If we are talking about millions of records this is going to be a "very" slow process. Why update all the records when english is needed, only to change them back the next time spanish is needed (at least that appears to be what you are doing). Why not just add a column to the table that references (foreign key) your tbl_glossario table and then "repeat" all the entries currently in your geral_texto table for each "language" with the substitutions already made, then when you want "english" just select where they are already in english. Updating all the records in the table each time a different language is needed seems like unnecssary work. If you really don't want to store for "every language" you could do a "view" that calculates the changes each time without permenantly storing them.
Just my opinion, but anyway here is the code:
if object_id('test_') is not null drop table test_
create table test_ (pk_ int identity, myText_ text)
insert into test_ (myText_) values ('This is test1')
insert into test_ (myText_) values ('This is test2')
insert into test_ (myText_) values ('This is Value3')
begin
declare @findString_ varchar(8000)
declare @replaceString_ varchar(8000)
declare @deleteLength_ int
declare @offSet_ int
declare @pk_ int
declare @myTextPtr_ binary(16) --varbinary
--NOTE: Your outer loop would start here and set the @findString_ and @replaceStr_ variable as appropriate
set @findString_ = 'test'
set @replaceString_ = 'My New Words'
set @deleteLength_ = len(@findString_)
--This is the inner loop to walk all the appropriate records
declare cur1_ cursor for
select pk_, textptr(myText_), patindex( '%' + @findString_ + '%', myText_) - 1
from test_
where patindex( '%' + @findString_ + '%', myText_) <> 0
open cur1_
fetch next from cur1_ into @pk_, @myTextPtr_, @offSet_
while @@fetch_status = 0
begin
UPDATETEXT test_.myText_ @myTextPtr_ @offSet_ @deleteLength_ @replaceString_
fetch next from cur1_ into @pk_, @myTextPtr_, @offSet_
end
CLOSE cur1_
DEALLOCATE cur1_
select * from test_
end
January 7, 2011 at 9:19 am
I am working in a SQL Server 2000 database and have a new requirement to convert the [Comments] filed to "NTEXT". Can anyone help me convert the code below to allow the data to be stored in "NTEXT"? It currently works if the field is varchar but the data requirements for this field has greatly increased so I need to change it to "NTEXT". Any and all help in this matter is greatly appreciated.
[Comments] = [Comments] + '[Auto de-escalted as infringement ' + Cast(@InfringementId as nvarchar) + ' marked inactive.]'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply