Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

like StringReplace() in MSAccess sql sentence?

Posted on 2003-10-28
14
Medium Priority
?
512 Views
Last Modified: 2010-04-05
Is there a function like StringReplace() ues in MS Access sql sentence?
0
Comment
Question by:yiyanxiyin
  • 5
  • 3
  • 3
  • +2
14 Comments
 
LVL 2

Expert Comment

by:alsantos
ID: 9638936
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
 

Expert Comment

by:ionet
ID: 9639068
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9639853
? how looks like the stringReplace-function in Access?
-i found only a Replace-function in Access, but no stringReplace

meikl ;-)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:yiyanxiyin
ID: 9640303
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9640615
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
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 9640787
Isn't there an Access forum at ExEx that would be a better place to ask this question?
0
 

Author Comment

by:yiyanxiyin
ID: 9647011
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
 
LVL 2

Expert Comment

by:alsantos
ID: 9647076
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
 
LVL 2

Expert Comment

by:alsantos
ID: 9647084
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
 
LVL 27

Accepted Solution

by:
kretzschmar earned 400 total points
ID: 9649451
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
 

Author Comment

by:yiyanxiyin
ID: 9654667
hi alsantos ,thank you very much, but i want a Access function not Delphi
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9655852
just to ask, why a c-grade?
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 9657178
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9657232
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question