New character functions have been developed that make it easier to understand and enter the required arguments. These functions have streamlined parameter lists, similar to those used by SQL functions. In some cases, these simplified functions provide slightly different functionality than previous versions of similar functions.
The simplified functions do not have an output argument. Each function returns a value that has a specific data type.
When used in a request against a relational data source, these functions are optimized (passed to the RDBMS for processing).
Note:
How to: |
The CHAR_LENGTH function returns the length, in characters, of a string. In Unicode environments, this function uses character semantics, so that the length in characters may not be the same as the length in bytes. If the string includes trailing blanks, these are counted in the returned length. Therefore, if the format source string is type An, the returned value will always be n.
CHAR_LENGTH(source_string)
where:
Alphanumeric
Is the string whose length is returned.
The data type of the returned length value is Integer.
The following request against the EMPLOYEE data source creates a virtual field named LASTNAME of type A15V that contains the LAST_NAME with the trailing blanks removed. It then uses CHAR_LENGTH to return the number of characters.
DEFINE FILE EMPLOYEE LASTNAME/A15V = RTRIM(LAST_NAME); END TABLE FILE EMPLOYEE SUM LAST_NAME NOPRINT AND COMPUTE NAME_LEN/I3 = CHAR_LENGTH(LASTNAME); BY LAST_NAME ON TABLE SET PAGE NOPAGE END
The output is:
LAST_NAME NAME_LEN --------- -------- BANNING 7 BLACKWOOD 9 CROSS 5 GREENSPAN 9 IRVING 6 JONES 5 MCCOY 5 MCKNIGHT 8 ROMANS 6 SMITH 5 STEVENS 7
How to: Reference: |
Given a number, DIGITS converts it to a character string of the specified length. The format of the field that contains the number must be Integer.
DIGITS(number,length)
where:
Integer
Is the number to be converted, stored in a field with data type Integer.
Integer between 1 and 10
Is the length of the returned character string. If length is longer than the number of digits in the number being converted, the returned value is padded on the left with zeros. If length is shorter than the number of digits in the number being converted, the returned value is truncated on the left.
The following request against the WF_RETAIL_LITE data source converts -123.45 and ID_PRODUCT to character strings:
DEFINE FILE WF_RETAIL_LITE MEAS1/I8=-123.45; DIG1/A6=DIGITS(MEAS1,6) ; DIG2/A6=DIGITS(ID_PRODUCT,6) ; END TABLE FILE WF_RETAIL_LITE PRINT MEAS1 DIG1 ID_PRODUCT DIG2 BY PRODUCT_SUBCATEG WHERE PRODUCT_SUBCATEG EQ 'Flat Panel TV' ON TABLE SET PAGE NOPAGE END
The output is:
How to: Reference: |
LPAD uses a specified character and output length to return a character string padded on the left with that character.
LPAD(string, out_length, pad_character)
where:
Fixed length alphanumeric
Is a string to pad on the left side.
Integer
Is the length of the output string after padding.
Fixed length alphanumeric
Is a single character to use for padding.
In the following request against the WF_RETAIL data source, LPAD left-pads the PRODUCT_CATEGORY column with @ symbols:
DEFINE FILE WF_RETAIL LPAD1/A25 = LPAD(PRODUCT_CATEGORY,25,'@'); DIG1/A4 = DIGITS(ID_PRODUCT,4); END TABLE FILE WF_RETAIL SUM DIG1 LPAD1 BY PRODUCT_CATEGORY ON TABLE SET PAGE NOPAGE ON TABLE SET STYLE * TYPE=DATA,FONT=COURIER,SIZE=11,COLOR=BLUE,$ END
The output is:
How to: |
The LOWER function takes a source string and returns a string of the same data type with all letters translated to lowercase.
LOWER(source_string)
where:
Alphanumeric
Is the string to convert to lowercase.
The returned string is the same data type and length as the source string.
In the following request against the EMPLOYEE data source, LOWER converts the LAST_NAME field to lowercase and stores the result in LOWER_NAME:
TABLE FILE EMPLOYEE PRINT LAST_NAME AND COMPUTE LOWER_NAME/A15 = LOWER(LAST_NAME); ON TABLE SET PAGE NOPAGE END
The output is:
LAST_NAME LOWER_NAME --------- ---------- STEVENS stevens SMITH smith JONES jones SMITH smith BANNING banning IRVING irving ROMANS romans MCCOY mccoy BLACKWOOD blackwood MCKNIGHT mcknight GREENSPAN greenspan CROSS cross
How to: |
The LTRIM function removes all blanks from the left end of a string.
LTRIM(source_string)
where:
Alphanumeric
Is the string to trim on the left.
The data type of the returned string is AnV, with the same maximum length as the source string.
In the following request against the MOVIES data source, the DIRECTOR field is right-justified and stored in the RDIRECTOR virtual field. Then LTRIM removes leading blanks from the RDIRECTOR field:
DEFINE FILE MOVIES RDIRECTOR/A17 = RJUST(17, DIRECTOR, 'A17'); END TABLE FILE MOVIES PRINT RDIRECTOR AND COMPUTE TRIMDIR/A17 = LTRIM(RDIRECTOR); WHERE DIRECTOR CONTAINS 'BR' ON TABLE SET PAGE NOPAGE END
The output is:
RDIRECTOR TRIMDIR --------- ------- ABRAHAMS J. ABRAHAMS J. BROOKS R. BROOKS R. BROOKS J.L. BROOKS J.L.
How to: |
The POSITION function returns the first position (in characters) of a substring in a source string.
POSITION(pattern, source_string)
where:
Alphanumeric
Is the substring whose position you want to locate. The string can be as short as a single character, including a single blank.
Alphanumeric
Is the string in which to find the pattern.
The data type of the returned value is Integer.
In the following request against the EMPLOYEE data source, POSITION determines the position of the first capital letter I in LAST_NAME and stores the result in I_IN_NAME:
TABLE FILE EMPLOYEE PRINT LAST_NAME AND COMPUTE I_IN_NAME/I2 = POSITION('I', LAST_NAME); ON TABLE SET PAGE NOPAGE END
The output is:
LAST_NAME I_IN_NAME --------- --------- STEVENS 0 SMITH 3 JONES 0 SMITH 3 BANNING 5 IRVING 1 ROMANS 0 MCCOY 0 BLACKWOOD 0 MCKNIGHT 5 GREENSPAN 0 CROSS 0
How to: |
The RTRIM function removes all blanks from the right end of a string.
RTRIM(source_string)
where:
Alphanumeric
Is the string to trim on the right.
The data type of the returned string is AnV, with the same maximum length as the source string.
The following request against the MOVIES data source creates the field DIRSLASH, that contains a slash at the end of the DIRECTOR field. Then it creates the TRIMDIR field, which trims the trailing blanks from the DIRECTOR field and places a slash at the end of that field:
TABLE FILE MOVIES PRINT DIRECTOR NOPRINT AND COMPUTE DIRSLASH/A18 = DIRECTOR|'/'; TRIMDIR/A17V = RTRIM(DIRECTOR)|'/'; WHERE DIRECTOR CONTAINS 'BR' ON TABLE SET PAGE NOPAGE END
On the output, the slashes show that the trailing blanks in the DIRECTOR field were removed in the TRIMDIR field:
DIRSLASH TRIMDIR -------- ------- ABRAHAMS J. / ABRAHAMS J./ BROOKS R. / BROOKS R./ BROOKS J.L. / BROOKS J.L./
How to: |
The SUBSTRING function extracts a substring from a source string. If the ending position you specify for the substring is past the end of the source string, the position of the last character of the source string becomes the ending position of the substring.
SUBSTRING(source_string, start_position, length_limit)
where:
Alphanumeric
Is the string from which to extract the substring. It can be a field, a literal in single quotation marks ('), or a variable.
Integer
Is the starting position of the substring in source_string. If the position is 0, it is treated as 1. If the position is negative, the starting position is counted backward from the end of source_string.
Integer
Is the limit for the length of the substring. The ending position of the substring is calculated as start_position + length_limit - 1. If the calculated position beyond the end of the source string, the position of the last character of source_string becomes the ending position.
The data type of the returned substring is AnV.
In the following request, POSITION determines the position of the first letter I in LAST_NAME and stores the result in I_IN_NAME. SUBSTRING then extracts three characters beginning with the letter I from LAST_NAME, and stores the results in I_SUBSTR.
TABLE FILE EMPLOYEE PRINT COMPUTE I_IN_NAME/I2 = POSITION('I', LAST_NAME); AND COMPUTE I_SUBSTR/A3 = SUBSTRING(LAST_NAME, I_IN_NAME, I_IN_NAME+2); BY LAST_NAME ON TABLE SET PAGE NOPAGE END
The output is:
LAST_NAME I_IN_NAME I_SUBSTR --------- --------- -------- BANNING 5 ING BLACKWOOD 0 BL CROSS 0 CR GREENSPAN 0 GR IRVING 1 IRV JONES 0 JO MCCOY 0 MC MCKNIGHT 5 IGH ROMANS 0 RO SMITH 3 ITH 3 ITH STEVENS 0 ST
How to: Reference: |
RPAD uses a specified character and output length to return a character string padded on the right with that character.
RPAD(string, out_length, pad_character)
where:
Alphanumeric
Is a string to pad on the right side.
Integer
Is the length of the output string after padding.
Alphanumeric
Is a single character to use for padding.
In the following request against the WF_RETAIL data source, RPAD right-pads the PRODUCT_CATEGORY column with @ symbols:
DEFINE FILE WF_RETAIL RPAD1/A25 = RPAD(PRODUCT_CATEGORY,25,'@'); DIG1/A4 = DIGITS(ID_PRODUCT,4); END TABLE FILE WF_RETAIL SUM DIG1 RPAD1 BY PRODUCT_CATEGORY ON TABLE SET PAGE NOPAGE ON TABLE SET STYLE * TYPE=DATA,FONT=COURIER,SIZE=11,COLOR=BLUE,$ END
The output is:
How to: |
The TOKEN function extracts a token (substring) based on a token number and a delimiter character.
TOKEN(string, delimiter, number)
where:
Fixed length alphanumeric
Is the character string from which to extract the token.
Fixed length alphanumeric
Is a single character delimiter.
Integer
Is the token number to extract.
TOKEN extracts the second token from the PRODUCT_SUBCATEG column, where the delimiter is the letter P:
DEFINE FILE WF_RETAIL_LITE TOK1/A20 =TOKEN(PRODUCT_SUBCATEG,'P',2); END TABLE FILE WF_RETAIL_LITE SUM TOK1 AS Token BY PRODUCT_SUBCATEG ON TABLE SET PAGE NOPAGE END
The output is:
How to: |
The TRIM_ function removes all occurrences of a single character from either the beginning of a string, the end of a string, or both.
TRIM_(trim_where, trim_character, source_string)
where:
Keyword
Defines where to trim the source string. Valid values are:
Alphanumeric
Is a single character, enclosed in single quotation marks ('), whose occurrences are to be removed from source_string. For example, the character can be a single blank (‘ ‘).
Alphanumeric
Is the string to be trimmed.
The data type of the returned string is AnV.
In the following request, TRIM_ removes leading occurrences of the character ‘B’ from the DIRECTOR field:
TABLE FILE MOVIES PRINT DIRECTOR AND COMPUTE TRIMDIR/A17 = TRIM_(LEADING, 'B', DIRECTOR); WHERE DIRECTOR CONTAINS 'BR' ON TABLE SET PAGE NOPAGE END
The output is:
DIRECTOR TRIMDIR -------- ------- ABRAHAMS J. ABRAHAMS J. BROOKS R. ROOKS R. BROOKS J.L. ROOKS J.L.
How to: |
The UPPER function takes a source string and returns a string of the same data type with all letters translated to uppercase.
UPPER(source_string)
where:
Alphanumeric
Is the string to convert to uppercase.
The returned string is the same data type and length as the source string.
In the following request, LCWORD converts LAST_NAME to mixed case. Then UPPER converts the LAST_NAME_MIXED field to uppercase:
DEFINE FILE EMPLOYEE LAST_NAME_MIXED/A15=LCWORD(15, LAST_NAME, 'A15'); LAST_NAME_UPPER/A15=UPPER(LAST_NAME_MIXED) ; END TABLE FILE EMPLOYEE PRINT LAST_NAME_UPPER AND FIRST_NAME BY LAST_NAME_MIXED WHERE CURR_JOBCODE EQ 'B02' OR 'A17' OR 'B04'; ON TABLE SET PAGE NOPAGE END
The output is:
LAST_NAME_MIXED LAST_NAME_UPPER FIRST_NAME --------------- --------------- ---------- Banning BANNING JOHN Blackwood BLACKWOOD ROSEMARIE Cross CROSS BARBARA Mccoy MCCOY JOHN Mcknight MCKNIGHT ROGER Romans ROMANS ANTHONY
iWay Software |