yiyanxiyin
asked on
like StringReplace() in MSAccess sql sentence?
Is there a function like StringReplace() ues in MS Access sql sentence?
Hey yiyanxiyn. I'm sure the StringReplace() function is a lot better than this, but I wrote this a long time ago when I needed to replace the 'f' with '1'. Here is what I wrote:
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls;
type
TForm1 = class(TForm)
Button1: TButton;
Edit1: TEdit;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.Button1Click(Sender : TObject);
var
i : integer; //this is the character counter
Str : string; //this is the whole string
str2: string; //this is the current string character
strpos : integer; //posistion of the string character
begin
Str := edit1.text; //making str get the value of edit1.text
for i := 1 to length(str) do begin //for loop
str2 := str[i]; //showing that str2 is equal to the current
//chracter in str
if str2 = 'f' then //if str2 has the value of f
begin
strpos := pos(str2,str); //strpos has the position of the
//character in str2
delete(str,strpos,1); //delete that char out of the string
insert('1',str,strpos);
end;
end;
edit1.text := str; //make edit1.text show str
end;
end.
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls;
type
TForm1 = class(TForm)
Button1: TButton;
Edit1: TEdit;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.Button1Click(Sender
var
i : integer; //this is the character counter
Str : string; //this is the whole string
str2: string; //this is the current string character
strpos : integer; //posistion of the string character
begin
Str := edit1.text; //making str get the value of edit1.text
for i := 1 to length(str) do begin //for loop
str2 := str[i]; //showing that str2 is equal to the current
//chracter in str
if str2 = 'f' then //if str2 has the value of f
begin
strpos := pos(str2,str); //strpos has the position of the
//character in str2
delete(str,strpos,1); //delete that char out of the string
insert('1',str,strpos);
end;
end;
edit1.text := str; //make edit1.text show str
end;
end.
? how looks like the stringReplace-function in Access?
-i found only a Replace-function in Access, but no stringReplace
meikl ;-)
-i found only a Replace-function in Access, but no stringReplace
meikl ;-)
ASKER
thanks all,but i want a function,it can be use in access sql sentence,for example:
select replace(name,'Jone','Mike' ) from info_person
but the function replace() is not found in access.
who can tell me what use replace() in access?
select replace(name,'Jone','Mike'
but the function replace() is not found in access.
who can tell me what use replace() in access?
well,
seems not to work with replace,
but you can use IIF instead like
select
state,
IIF([State] = "NT","XX",[State]) as y
from ATable
meikl ;-)
seems not to work with replace,
but you can use IIF instead like
select
state,
IIF([State] = "NT","XX",[State]) as y
from ATable
meikl ;-)
Isn't there an Access forum at ExEx that would be a better place to ask this question?
ASKER
hi kretzschmar,thanks again
but the function iif() is not better,it can't finish my work,for example:
i have a table with data like this:
f1 f2
1 a001
2 a002
3 a003
but how can i get the result like this:
f1 f2
1 b001
2 b002
3 b003
but the function iif() is not better,it can't finish my work,for example:
i have a table with data like this:
f1 f2
1 a001
2 a002
3 a003
but how can i get the result like this:
f1 f2
1 b001
2 b002
3 b003
Hi yiyanxiyin, you can try somethink like this:
procedure TForm1.Button1Click(Sender : TObject);
begin
while (not Table1.Eof) do
begin
Table1.FieldByName('NameOf Field').As String:=St ringReplac e(Table1.F ieldByName ('NameOfFi eld').AsSt ring, 'a', 'b', [rfReplaceAll]);
Table1.Next;
end;
end;
or
procedure TForm1.Button1Click(Sender : TObject);
begin
while (not Table1.Eof) do
begin
Table1.Fields[0].Text:=Str ingReplace (Table1.Fi elds[0].Te xt, 'a', 'b', [rfReplaceAll]); // Fields[0] if the field you want replace is the first one.
Table1.Next;
end;
end;
alsantos
procedure TForm1.Button1Click(Sender
begin
while (not Table1.Eof) do
begin
Table1.FieldByName('NameOf
Table1.Next;
end;
end;
or
procedure TForm1.Button1Click(Sender
begin
while (not Table1.Eof) do
begin
Table1.Fields[0].Text:=Str
Table1.Next;
end;
end;
alsantos
sorry... not that... try this:
procedure TForm1.Button1Click(Sender : TObject);
begin
while (not Table1.Eof) do
begin
Table1.Edit;
Table1.FieldByName('NameOf Field').As String:=St ringReplac e(Table1.F ieldByName ('NameOfFi eld').AsSt ring, 'a', 'b', [rfReplaceAll]);
Table1.Post;
Table1.Next;
end;
end;
procedure TForm1.Button1Click(Sender : TObject);
begin
while (not Table1.Eof) do
begin
Table1.Edit;
Table1.Fields[0].Text:=Str ingReplace (Table1.Fi elds[0].Te xt, 'a', 'b', [rfReplaceAll]);
Table1.Post;
Table1.Next;
end;
end;
I didn't test but I think its work.
alsantos
procedure TForm1.Button1Click(Sender
begin
while (not Table1.Eof) do
begin
Table1.Edit;
Table1.FieldByName('NameOf
Table1.Post;
Table1.Next;
end;
end;
procedure TForm1.Button1Click(Sender
begin
while (not Table1.Eof) do
begin
Table1.Edit;
Table1.Fields[0].Text:=Str
Table1.Post;
Table1.Next;
end;
end;
I didn't test but I think its work.
alsantos
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi alsantos ,thank you very much, but i want a Access function not Delphi
just to ask, why a c-grade?
Another question: why wasn't this question asked at https://www.experts-exchange.com/Databases/MS_Access/ ???
Answer: It is the first question from a new ExEx member who has only explored the Delphi section so far... ;-)
Answer: It is the first question from a new ExEx member who has only explored the Delphi section so far... ;-)
nono, workshop_alex,
i guess the questioner wanted to use the replace-function with a tadoquery,
which not work with ado, but within access it will work
so i would not say, that this question is posted in a wrong topic
more worry i have about the c-grade,
so i must reask yiyanxiyin:
why a c-grade?
meikl ;-)
i guess the questioner wanted to use the replace-function with a tadoquery,
which not work with ado, but within access it will work
so i would not say, that this question is posted in a wrong topic
more worry i have about the c-grade,
so i must reask yiyanxiyin:
why a c-grade?
meikl ;-)
procedure TForm1.Button1Click(Sender
begin
Edit1.Text:='TheTextWeWant
Edit1.Text:=StringReplace(
end;
alsantos