SQL ReplaceString

[danten]

Hi all.
How to write a SQL query to change the text in a table recursively?
Here is the code for an operation with a database table:

Delphi-Quellcode:

try
    tab_1.First;
  while not tab_1.Eof do
  begin
    with tab_1 do
    begin
      Edit;
      Application.ProcessMessages;
      Fieldbyname('URL').value := StringReplace(Fieldbyname('URL').AsString,'.','',[rfReplaceAll, rfIgnoreCase]);
      Fieldbyname('URL').value := StringReplace(Fieldbyname('URL').AsString,'/','-',[rfReplaceAll, rfIgnoreCase]);
      Fieldbyname('TEXT').value :=  StringReplace(tab_2.Fieldbyname('TEXT').AsString,'=','',[rfReplaceAll, rfIgnoreCase]);

      Application.ProcessMessages;
      Post;
    end;
      tab_1.Next;
    end;

  finally
    frm_convert.Caption := 'PtoP => Done';
  end;

Thank you very much in advance.

[Dejan Vu]

What is recursively in your example?

Code:

update table set URL = replace (URL,'.','')
update table set URL = replace (URL,'/','-')
...

The existence and concrete syntax for 'replace' depends on the RDMS you are using (I am not familiar with ADS). Also, it might be possible to combine the replace-statements in one single update-statement.

[danten]

Zitat von Dejan Vu:

What is recursively in your example?

Code:

update table set URL = replace (URL,'.','')
update table set URL = replace (URL,'/','-')
...

The existence and concrete syntax for 'replace' depends on the RDMS you are using (I am not familiar with ADS). Also, it might be possible to combine the replace-statements in one single update-statement.

Thanks to small changes work.

Delphi-Quellcode:

Query1.SQL.Clear;
Query1.SQL.Add('Update [tab_1] set [URL] = "REPLACE ([URL], ""."", """")"');
Query1.SQL.Add('Update [tab_1] set [URL] = "REPLACE ([URL], ""/"", ""-"")"');
Query1.ExecSQL;
......

Thanks Daniel

[Dejan Vu]

Are you sure it worked? It looks as if you replace the contents of your column 'URL' with a string like 'REPLACE ([URL], ".", "")' and I doubt this is what you want.

[danten]

Zitat von Dejan Vu:

Are you sure it worked? It looks as if you replace the contents of your column 'URL' with a string like 'REPLACE ([URL], ".", "")' and I doubt this is what you want.

??????????????????????????

Delphi-Quellcode:

update produkt set URL = replace (URL,'.',''); 
 =>> 
[4.8.2014 12:49:11] Executing Query: 
update produkt set URL = replace (URL,'.','');
[4.8.2014 12:49:11] Token ',' expected, but 'replace' found at line 1, column 38 - Native error: 30222

??????????????????????????

[mkinzler]

Absolute Database doesn't kow of an (string-)REPLACE function

http://www.componentace.com/help/abs...gfunctions.htm

If your're sure, that's only one needle:

update table set URL = substring ( URL,1, pos('.', URL)-1 ) || substring ( URL, pos('.', URL)+1 );