Helpful @ Functions in IDEA
@ISBLANK(field) returns records for which the field is blank
@TRIM(field) removes all trailing blank
spaces from the field
@LTRIM(field) removes all leading blank spaces from the field *vf
@LEN(field) returns the length of a field (includes blanks)
@LEN(@TRIM(field)) returns length of field excluding blanks
example: @LEN(@TRIM(invoice_number)) <> 9 returns all records where the length of the invoice_number field is not 9 – including those that are blank.
@UPPER(field) converts field contents to all upper case letters *vf
@STRIP(field) removes all internal spaces (not trailing) and punctuation and control characters *vf
example: @STRIP(name) “A. J. Smith” becomes “AJSmith”
@MID(field,x,x) returns information within a field based on position *vf
example: @MID(DL,5,6) “DL20840801-346” becomes “840801”
@REMOVE(field,””) removes a specific designated character from the field *vf
example: @REMOVE(code,”-“) 123-456 becomes 123456
@ISINI(“”,field) searches within a string for designated text and returns start position (0 if not found). ISIN is same, but case sensitive so not used as often.
example: @ISINI(“main”,address) <> 0 returns records that have “main” anywhere in the address field
@SOUNDSLIKE(field,””) uses the Soundex algorithm to return records that have a similar sound to what is specified
example: @SOUNDSLIKE(lname,”Wallace”) returns both Wallace and Wallis. Use is not limited to people's names.
@COMPARENOCASE(field1, field2) compares two fields ignoring case and returns -1,0,=1
example: @COMPARENOCASE(DEPT_CODE, DEP_NUMBER) <> 0 compares the two fields (resulting from a join) and returns those where they don’t match – for example employee department codes from payroll file with that from HR file.
@DOW(date field) returns the day of the week for the date field *vf
*vf = create a new virtual field when using these
- R. S. Kulzick – 4/19/2007 -