Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UPDATETEXT


UPDATETEXT

Author
Message
Fabio Reis Martins
Fabio Reis Martins
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 37

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


-=JLK=-
-=JLK=-
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 303

--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.


Fabio Reis Martins
Fabio Reis Martins
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 37

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,


-=JLK=-
-=JLK=-
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 303

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.


Fabio Reis Martins
Fabio Reis Martins
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 37

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


-=JLK=-
-=JLK=-
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 303

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


dbrownell
dbrownell
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
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.]'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search