Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UPDATETEXT Expand / Collapse
Author
Message
Posted Friday, May 18, 2007 12:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 19, 2010 12:21 PM
Points: 10, Visits: 31

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

 

Post #367326
Posted Friday, May 18, 2007 2:00 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 1:16 PM
Points: 367, 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.

Post #367345
Posted Friday, May 18, 2007 3:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 19, 2010 12:21 PM
Points: 10, Visits: 31

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,

 

 

 

Post #367369
Posted Monday, May 21, 2007 7:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 1:16 PM
Points: 367, 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.

Post #367622
Posted Wednesday, May 23, 2007 8:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 19, 2010 12:21 PM
Points: 10, Visits: 31

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

Post #368294
Posted Wednesday, May 23, 2007 9:40 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 1:16 PM
Points: 367, 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

 

Post #368320
Posted Friday, January 7, 2011 9:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 10, 2011 12:56 PM
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.]'
Post #1044491
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse