Solved

like StringReplace() in MSAccess sql sentence?

Posted on 2003-10-28
14
509 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
Independent Software Vendors: 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

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…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month11 days, 4 hours left to enroll

628 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