Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2014
    Posts
    1

    Unanswered: Using loops in ACE select statement

    Hi,
    I need to replace any any alpha characters in an ACE report with specific numbers.

    A,B,C = 1; D,E,F = 2 etc.

    Records can have more than one alpha.

    I can use a CASE statement but that only handles a single instance of a letter, since as soon as the condition is met it moves on to the next record.
    eg: "0123A456" becomes "01231456" just fine, but "012AB456" will only get turned into "0121B456" - the "B" doesn't get processed.

    What I'd like to do is use a loop of some sort to keep looping through the CASE until all the letters are replaced.

    Field is only 8 char long, so loop needs to run a maximum of 8 times.

    Cheers
    Dave

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Dave,

    This is definately something to be done with Perl, using something like:
    $Value is the initial value of your field
    Code:
    $Value =~ s/[ABC]/1/g;
    $Value =~ s/[DEF]/2/g;
    $Value =~ s/[GHI]/3/g;
    etc...
    the 'g' thing means the substitution will be made many times
    Something more beautiful can be done with a predefined array, but it is saturday night and I worked all day...

    Good luck
    Eric

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
  •