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 -

Home

Main Page

Contact

Contact rkulzick@stu.edu with questions or comments about this web site.
Copyright © 2007 Raymond S. Kulzick - Last modified: September 13, 2008