Making @LEFT, @MID, and @RIGHT Dynamic versus Static Functions

Have you ever had those character fields where you just want a piece of information carved out and a simple @Left, @right, or @mid won’t work because the records in the field vary in length? Consider nesting other functions with those mentioned above to create some amazing one step results.

 NB. Some of the examples below can also be completed with the @SPLIT or the new @SimpleSplit function available in IDEA Version Nine.

 Examples: PCODE Field (C)

1. AB12-4564/990013
2. V1-4459/100092
3. V11-440/120072

 Problem 1:

 Extract everything up to the dash to a new field.

 Equation 1:

 @LEFT(PCODE, @ISINI(“-“,PCODE)-1)

 What’s Happening:

 You are telling IDEA to look at the left most position of the PCODE field and return all the data up until it finds a “-“ in the field. The -1 is there so the “-“ is not included in the output.

Problem 2:

 Extract everything between the “–“ and the “/” to a new field.

 Equation 2:

 @MID(PCODE,@isini(“-“,PCODE)+1,@isini(“/”,PCODE)-1)

 What’s Happening:

 You are telling IDEA to look somewhere in the middle of the PCODE field. The starting point will be at the first “-“ found. The +1 is needed so the “-“ is not returned in the output. The end point will be when IDEA finds a “/” in the field. The -1 is there so the “/“ is not included in the output.

 Problem 3:

 Extract all but the last two characters to a new field.

 Equation 3:

 @LEFT(PCODE, @LEN(PCODE)-2)

What’s Happening:

You are telling IDEA to look at the left most position of the PCODE field and based on the individual lengths of each record, return everything but the last two characters, which is the purpose of the -2.

Examples: CUSTID Field (C) – A customer identification field

1. 34598
2. 00459
3. Pac123
4. 000989
5. 0xyz456
6. 00dwC224
7. 013456890290
8. WP39956078
9. Cen99
10. xTc675

Problem 4:

Extract the customer identification information to a new field less the leading zeroes

Equation 4:

@if(@left(CUSTID,1)="0",@mid(CUSTID,@FindOneOf(CUSTID,@RegExpr(CUSTID,"[A-z]|[1-9]")),10),CUSTID)

What’s Happening:

You are telling IDEA to look at the left most character in CUSTID and if it finds a zero (0), go to the middle of the field where either a number other than zero or any uppercase or lower case letter exists. Start from that point and pick up the next 10 characters. If the left most character is not a zero (0) return what was originally in the CUSTID field.

This is a complex equation in that your start parameter for the @mid function is a double nested function. Normally you use “abc” or “123” as a parameter with the @FindOneOf function, but in our case the leading zero can be followed by either a letter or a number and the letter can be either upper or lower case. This is why we make @FindOneOf a dynamic function in this example using the @RegExpr function to tell IDEA to find any letter either upper or lower case [A-z] or (|) {that’s a pipe symbol} any number from [1-9]