like StringReplace() in MSAccess sql sentence?

Is there a function like StringReplace() ues in MS Access sql sentence?
yiyanxiyinAsked:
Who is Participating?
 
kretzschmarConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
kretzschmarCommented:
? how looks like the stringReplace-function in Access?
-i found only a Replace-function in Access, but no stringReplace

meikl ;-)
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.