Solved

like StringReplace() in MSAccess sql sentence?

Posted on 2003-10-28
14
508 Views
Last Modified: 2010-04-05
Is there a function like StringReplace() ues in MS Access sql sentence?
0
Comment
Question by:yiyanxiyin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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!

 

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 200 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

738 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