Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013
    Posts
    8

    Unanswered: Remove a prefix from a selected field

    I have another question! I have the following query:-

    SELECT AccountNumber
    FROM BankDetails

    I am moving data from one place to another and need to remove a 'IBAN' prefix, which would be easy to do, but the data is not consistent, for example of the Account Number stored within the BankDetails table

    AccountNumber
    0123456789
    0987654321
    IBAN02222222
    IBAN11111111

    Therefore, i nee to have the output as follows, i.e. if the AccountNumber is prefixed with IBAN, then remove it.

    AccountNumber
    0123456789
    0987654321
    02222222
    11111111

    Again, thank you in advance for your assistance

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    case when length(AccountNumber) > 4 and substr(AccountNumber,1,4) = 'IBAN' then substr(AccountNumber,5) else AccountNumber end

    Andy

  3. #3
    Join Date
    Jul 2013
    Posts
    8
    Andy, excellent, thank you so much, it work a treat

Posting Permissions

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