Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2015

    Question Unanswered: Replace First Occurrence of a String

    Here are the 2 tables that I am working with:




    Account Conversion
    0041 10000151
    0051 10000152
    0052 10000153
    0053 100001524

    I need to update the Account table using the Conversion table by replacing the first 4 digits in the Account table with the Conversion value. For example, the first record should be updated from 0041980041 to 10000151980041.

    I'd really appreciate any suggestions! I've been stuck on this for awhile :/

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    use an update query, such as:-
    update mytable
    set Account.AccoutnNo = conversion.conversion & mid(account.accountNo,5) where
    left(account.accoutnno,4) = conversion .Account

    ..assuming that you have:-
    a column called accoutnno in account
    two columns in conversion labelled account and conversion

    the mid function call mid(account.accountNo,5) says startiong at characxter 5 take all the characters remainign in the string account.accountNo
    the left function call left(account.accoutnno,4) returns the the first 4 characters in account.accountno and the rest of the where caluse compares that to conversion.account. you coudl (and probably should use the more modern syntax of a JOInj in place of a where ckause, but I doubt it will matter

    BUT before you update dtaa then develop the query using SELECT till you know the query is correct
    work on a backup copy of the table/database till you know its correct.
    an alternative appraoch to that would be to create a temporary table as part of the migration process.

    OldAccountNo TEXT 10
    NewAccoutnNo Text 20 'cos some bright spark will want to increase this account n umber at some stage, setting to 20 gives 6 characters for them the (*&^*&() around with without wasting further development time. but if you trust your users make it 14

    then create a query that copies all the old accounts into that new table

    INSERT INTO MigrationTable OdAccoutnNo select AccountNo from Account

    then develop your query
    update MigrationTable
    set MigrationTable.OldAccoutnNo = conversion.conversion & mid(MigrationTable.OldAccoutnNo,5) where
    left(MigrationTable.OldAccoutnNo,4) = conversion .Account

    check the data of the converted accounts looks right, or better yet get your users to check that data, after all this is a conversion process foisted on development by users on their data. they need to take ownership of the changes as its their data, not yours

    then as Im a profound cynic (I deal with far to many Accountants for my mental health) do a check yourself to make certain there are no old accounts in the new table that don't have a matching new account number

    select OldAccountNo from MigrationTable where isnull(NewAccountNo) = vbtrue or len(NewAccountNo) <=0

    then clear any of those
    the advantage of this approach is
    1) you make changes to copies of data, all exisiting data is left untouched UNTILL such time as you and your users decide the updated data ius right and therefore can be used
    2) if things go wrong then you change the proposed changes NOT unpick errors in live data
    3) if you have a mind to you could leave the migration table in the db for a while (say a year) so if ever some weasel, sorry user, from Accounts copmes over and ask why you or the computer upset their systems you can prove the error of their ways.*

    once you and your users are happy on the data (and in an ideal world get them to sign off on it so they can't claim they never looked at it, its all your fault their data is to cock, etc,etc....) then run an update query which copies the new account number from the migration table to the account table AFTER changing the column width appropriately.

    UPDATE Account set AccoutnNo = migrationable.NewAccoutnNo
    left join Account on Account.AccoutnNo = MigrationTable.OldAccountNo

    then again becuase Im a bitter warped twisted cynic I'd run another query which checked that every account in the old table is now 14 characters long.

    select Account.AccountNo from Account where Account where len(Account) <14

    assuming you find any grab your baseball bat and go pay a visit to your users and suggest the error of their ways. If your prefered way of discussing these sort of erorrs is to hold a necklace party with your accountant please feel free to use the tyre or your choice

    *and before anyone thinks I have no heart I do have a soft sport for Accountants, its the patches quicksand on Maltreath near Bangor
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts