like StringReplace() in MSAccess sql sentence?

Is there a function like StringReplace() ues in MS Access sql sentence?
yiyanxiyinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

alsantosCommented:
Hi yiyanxiyin, the StringReplace exist and you can use like this example:

procedure TForm1.Button1Click(Sender: TObject);
begin
Edit1.Text:='TheTextWeWant';
Edit1.Text:=StringReplace(edit1.text,'We','You',[rfReplaceAll]);
end;

alsantos
0
ionetCommented:
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.
0
kretzschmarCommented:
? how looks like the stringReplace-function in Access?
-i found only a Replace-function in Access, but no stringReplace

meikl ;-)
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

yiyanxiyinAuthor Commented:
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?
0
kretzschmarCommented:
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 ;-)
0
Wim ten BrinkSelf-employed developerCommented:
Isn't there an Access forum at ExEx that would be a better place to ask this question?
0
yiyanxiyinAuthor Commented:
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



0
alsantosCommented:
Hi yiyanxiyin, you can try somethink like this:

procedure TForm1.Button1Click(Sender: TObject);
begin
  while (not Table1.Eof) do
  begin
    Table1.FieldByName('NameOfField').AsString:=StringReplace(Table1.FieldByName('NameOfField').AsString, 'a', 'b', [rfReplaceAll]);
    Table1.Next;
  end;
end;

or

procedure TForm1.Button1Click(Sender: TObject);
begin
  while (not Table1.Eof) do
  begin
    Table1.Fields[0].Text:=StringReplace(Table1.Fields[0].Text, 'a', 'b', [rfReplaceAll]);  // Fields[0] if the field you want replace is the first one.
    Table1.Next;
  end;
end;

alsantos
0
alsantosCommented:
sorry... not that... try this:

procedure TForm1.Button1Click(Sender: TObject);
begin
  while (not Table1.Eof) do
  begin
    Table1.Edit;
    Table1.FieldByName('NameOfField').AsString:=StringReplace(Table1.FieldByName('NameOfField').AsString, '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:=StringReplace(Table1.Fields[0].Text, 'a', 'b', [rfReplaceAll]);
    Table1.Post;
    Table1.Next;
  end;
end;

I didn't test but I think its work.

alsantos
0
kretzschmarCommented:
in your case a select like

Select
  f1,
  iif(left([f2],1) = "a","b"+mid([f2],2),[f2]) as f2
from ATable

should do it

meikl ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yiyanxiyinAuthor Commented:
hi alsantos ,thank you very much, but i want a Access function not Delphi
0
kretzschmarCommented:
just to ask, why a c-grade?
0
Wim ten BrinkSelf-employed developerCommented:
Another question: why wasn't this question asked at http://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... ;-)
0
kretzschmarCommented:
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 ;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.