update field - add more data after last character

Posted on 2012-09-11
Last Modified: 2012-09-12
I know how to use the Update command to update a field with new information, but what if I want to add info to the field after the last character.

For Example, I have a string field were we store email addresses.  I want to update a this field for a bunch of records, but I don't want to overwrite the data, instead I need to add to it at the end of the field.  The existing data for each record will be different, so is important to leave the existing data alone and just add to it.

modifiy the current email field from this:
investor.i_email =

to this:
investor.i_email =;

I know that for updating this field I would use the following command line argument:
UPDATE investor set i_email="" WHERE taxid='11-1111111'

My guess is that to add to this field it would be just as simple but with a different command?

I tried the following but it did not work:
UPDATE investor set i_email = i_email + ';' WHERE taxid='11-1111111'

Question by:IO_Dork
    LVL 27

    Expert Comment

    CREATE CURSOR test (name C(50))
    REPLACE name WITH 'Cyril'
    UPDATE test SET name = 'Captain ' + name WHERE .T.
    LVL 27

    Accepted Solution

    UPDATE investor set i_email = ALLTRIM(i_email) + '; ' +' WHERE taxid='11-1111111

    it is keeping the spaces at the end. You need to trim the field.
    LVL 29

    Expert Comment

    by:Olaf Doschke
    If you intend to write C(100) into a c(50) field for example, that's only possible by ALTER TABLE and extending field lengths. The only fields allowing any length by default are memo.

    If you want to use the spaces you still have free in an email field, you can do that as Cyril dhows in his second answer.

    Just remember, field+suffix will be len(field)+len(suffix), therefore the suffix never will appear in the field, as it's cut off, because it extends the string after the spaces, which are also already in a field. Even if you visually only see 20 chars of a 50 char length field used it's always padded to the right with spaces, so you need Alltrim or Rtrim at least to add something in the ramaining area of right spaces unused.

    Longwinding explanation shortened: I'd not do this at all, but add another field for new data. Don't misuse some left over space to store data, that should go into a new field. You will face problems most probably with one mail address leaving too less space to add your further data. And would need to change all code using the email field to only take the left part of it, if the concatenated data does not belong to the mail address, this is just breaking the application.

    If you're not allowed to change database, then either add new tables 1:1 related or put these even into a new database to leave the old one alone.

    Bye, Olaf.
    LVL 41

    Expert Comment

    I agree everything written above I would just add two points:

    1) If you use - (minus) operator then it will also work:
    UPDATE investor set i_email = i_email - ';' WHERE taxid='11-1111111'

    2) I would even recommend to have a separate table for e-mail addresses. It will have just two fields: InvestorID, and i_Email and it can collect different number of e-mails for every investor (suppose InvestorID is the primary key in Investor table). This data model is much more flexible than to add new fields to the existing table if there is not enough space...

    Author Comment

    I am a little confussed.  Just to make sure we are on the same page let me clearify what I am looking for.

    i have a string field where we enter email addresses.  I have a client that has 200 accounts with us, therefore I have 200 account records in foxpro for this client.  They requested us to update their account records with an additional email accross all 200 accounts.  Each account email field varies in content for this client, but the email we need to add will be amended to all accounts.  My Front end system does not allow for bulk updating of this string field accross multiple accounts, so i need to do through the backend in foxpro and use a statement in foxpro to update these 200 accounts.  I don't want to overwrite the contents, just add to them.
    LVL 12

    Expert Comment

    Also the syntax that you use will depend on where your data table 'lives'.

    If this is being done in a VFP data table, then all of the VFP syntax would be applicable.

    But if this change were being done in a data table 'living' in another data backend, such as SQL Server, then you would have to use syntax compatible with that backend.

    For Example:
        Functions like ALLTRIM() do not exist, as such, in SQL Server
        You would need to use TRIM()

    Good Luck

    Author Comment

    its in a vfp dbf free table.

    Author Comment

    the email field is just a stringfield allowing upto 120 characters.
    LVL 41

    Expert Comment

    UPDATE investor set i_email = i_email - ';' WHERE taxid='11-1111111'
    will work for you.

    You may also test if there is enough space in the field:
    SELECT * FROM investor WHERE 120 - LEN(TRIM(i_email)) < LEN(';')
    If it displays no rows then everything is OK.

    Author Closing Comment

    Thanks CaptainCyril and thanks to all who contributed.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: (http://msd…
    There are many benefits to finding online courses that align with your personal or career goals. Read more about our reasons for continuing your education in technology.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 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

    16 Experts available now in Live!

    Get 1:1 Help Now