Solved

Need Query...

Posted on 2002-04-10
20
162 Views
Last Modified: 2010-04-04
Hi All,
i use interbase, and i need query that can allow me to update table with Specific word in the Field "Change word with other" how can i do this.....

Abdelghani
0
Comment
Question by:Gani2001
  • 6
  • 3
  • 3
  • +5
20 Comments
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6932634
Hi,

update tablename set fieldname="new word" where fieldname="old word"

So, if you had a table called WORLDLIST and the field you wanted to modify was WORDS and the word you wanted to change was EXPERTS and you wanted to chance it to EXCHANGE, you would do this:

update WORDLIST set WORDS="EXCHANGE" where WORDS="EXPERT"

Is this what you mean?

Cheers,

Stu
0
 
LVL 1

Expert Comment

by:TAZI
ID: 6933328
Hi ..

As Stuart Johnson suggests ...

Update <TableName> set <FieldName> = "NEW WORD" where <FieldName> = "OLD WORD"

Regards
TAZI
0
 
LVL 1

Author Comment

by:Gani2001
ID: 6933399
no, i need to change spesfic word from sentance
if i had FieldName "Name"
ID Name
1  Mark Smith
2  Willy Smith
3  Smith Jone
i need to Change Smith to "Jak" i hope u understand what i mean

Abdelghani
0
 
LVL 1

Author Comment

by:Gani2001
ID: 6933404
no, i need to change spesfic word from sentance
if i had FieldName "Name"
ID Name
1  Mark Smith
2  Willy Smith
3  Smith Jone
i need to Change Smith to "Jak" i hope u understand what i mean

Abdelghani
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6933412
I don't think that's possible to do within a query.  I could tell you how to do it in MS-SQL as a stored procedure, and maybe even in Interbase with a stored procedure, but as a query, I think it's impossible.
0
 
LVL 1

Author Comment

by:Gani2001
ID: 6933415
no, i need to change spesfic word from sentance
if i had FieldName "Name"
ID Name
1  Mark Smith
2  Willy Smith
3  Smith Jone
i need to Change Smith to "Jak" i hope u understand what i mean

Abdelghani
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6933435
AFAIK the only way you could do this SIMPLY is to break the names into first and surnames then use the query I posted above.  The problem is, you need to do this in three steps - something that is impossible to do within on single query.

1. Select all names where name like "%Smith"
2. (no code available) replace Smith with Jak
3. Post this data back

If you can live with doing it in Delphi code, I can show you how to do that.  But I'm pretty confident in saying that it can't be done with a single query.
0
 
LVL 1

Expert Comment

by:TAZI
ID: 6933440
Hi ..

So if I understand this correctly, you want to change the Smith in all 3 records to Jak.

You final read would retrieve.

1. Mark Jak
2. Willy Jak
3. Jak Jone

Am I correct ?

You can easily do this in sql server using the Replace Statement.  Interbase, I don't know much about and I don't have any clue whether such a command exists.

Regards
TAZI
0
 
LVL 1

Author Comment

by:Gani2001
ID: 6933450
in delphi there is delay for doing that deu to using loop
and my table is very big about 81000 record

Abdelghani
0
 
LVL 1

Author Comment

by:Gani2001
ID: 6933451
Yes TAZI, some thing like replace...
but how in interbase6

abdelghani
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Comment

by:Gani2001
ID: 6933457
Yes TAZI, some thing like replace...
but how in interbase6

abdelghani
0
 
LVL 1

Expert Comment

by:TAZI
ID: 6933537
Hi,

As mentioned, I don't use Interbase and my knowledge there of is very limited.

I make use of SQL Server .... What you require can be done in SQL Server using the REPLACE Command.

eg.
  Replace(<FieldName>, 'OLD WORD', 'NEW WORD')

Table: TEMP

ID  NAME
 1  Mark Smith
 2  Willy Smith
 3  Smith Jone

Select replace(Name, 'SMITH', 'JAK') from TEMP

This will replace all SMITH to JAK ...

I am sorry, but I have no idea how this can be done in INTERBASE.  The above is SQL Code.

Hope this helps

Regards
TAZI
0
 
LVL 7

Expert Comment

by:God_Ares
ID: 6936615
i can not be done with an update.. try this..

procedure TForm1.Button1Click(Sender: TObject);
var SearchString,ReplaceString,s:String;
    i:integer;
begin
  SearchString := 'a';
  ReplaceString := '#';
  table1.First;
  while not table1.Eof do
  Begin
    s := table1.Fields.Fields[1].AsString;

    i := pos(SearchString,s);
    if i>=1 then // if searchpattern is reconized
    Begin
      delete(s,i,length(SearchString));
      Insert(ReplaceString,s,i);
      table1.Edit;

      table1.Fields.Fields[1].AsString := s;
      table1.Post;
    end;
    table1.Next;
  end;
end;
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6942274
or code a udf
(user defined function)
which is easy to do
and has the advantage
that the udf is
available in interbase,
so that it could
be done also with isql (for ex)

just ask,
if advice for udf is needed

meikl ;-)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6960357
If the IB SQL engine supports it, you CAN do this in an UPDATE statement.  However, you can only change one instance of SMITH per row.

This is only possible if IB SQL has the following (or equivalent) functions:
* Substring - function that returns part of the input/parameter string.
* StringLength - function that returns the length of the input/parameter string.
* Instring - function that returns the position of one character string inside another string, or zero if the string isn't found inside the other string.
* || - operator that concatenates strings.

Example:
Update tablename
Set NameCol = Substring(NameCol,1,Instring(NameCol,'SMITH')-1) || 'JAK' || Substring(NameCol,Instring(NameCol,'SMITH')+StringLength('SMITH')+1,StringLength(NameCol))
Where NameCol Like '%SMITH%';

Note 1: Run this update query multiple times if more than one 'SMITH' string might appear in a name.

Note 2: Consider normalizing the name into Lastname and Firstname columns.

================================================
God_Ares,
Please submit comments instead of proposed solutions.
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 75 total points
ID: 6960449
Instring isn't available, bur could be coded in a udf

udf=User Defined Function

udf's can be coded with delphi, placed in a dll,
which is deployed in the lib-directory of the inerbase-server.

meikl ;-)
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6975933
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101 or Netminder will return to finalize these if they are still open in 14 days.  Experts, please post closing recommendations before that time.

Below are your open questions as of today.  Questions which have been inactive for 21 days or longer are considered to be abandoned and for those, your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.  

Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added.  When you grade the question less than an A, please comment as to why.  This helps all involved, as well as others who may access this item in the future.  PLEASE DO NOT AWARD POINTS TO ME.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.20115277.html
http://www.experts-exchange.com/questions/Q.20287420.html

To view your locked questions, please click the following link(s) and evaluate the proposed answer.
http://www.experts-exchange.com/questions/Q.20287205.html

*****  E X P E R T S    P L E A S E  ******  Leave your closing recommendations.
If you are interested in the cleanup effort, please click this link
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
POINTS FOR EXPERTS awaiting comments are listed in the link below
http://www.experts-exchange.com/commspt/Q.20277028.html
 
Moderators will finalize this question if in @14 days Asker has not responded.  This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
 
Thanks everyone.
Moondancer
Moderator @ Experts Exchange
0
 

Expert Comment

by:SpideyMod
ID: 8280687
All,
I am unlocking this question in preparation for cleanup.  I will return in 7 days to finalize this question.  Please leave any recommendations for the final state of this question, I will take all recommendations into consideration.  Failing any feedback, I may decide in 7 days to delete or PAQ this question with no refund.  Thanks.

SpideyMod
Community Support Moderator @Experts Exchange
0
 
LVL 7

Expert Comment

by:God_Ares
ID: 8282013
don't care who get's points, I would like it, but I think my old buddy kretzschmar would like to get some too.

I just hope this action of SpideyMod will revive EE.
0
 

Expert Comment

by:SpideyMod
ID: 8335786
Split.  
God_Ares, points for you at:
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_20586081.html

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

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…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now