AnV fields, which represent variable length data types supported by relational database management systems, can be used as arguments in any function that requires an alphanumeric argument. There are also character functions created specifically for use with AnV fields. These are:
The following affect the use of an AnV field in a function:
How to: |
LENV returns the actual length of an AnV field or the size of an An field.
LENV(string, outfield)
where:
Alphanumeric of type An or AnV
Is the source field or an alphanumeric constant enclosed in single quotation marks. If it is a field, it can have An or AnV format. If it is a field of type AnV, its length is taken from the length bytes stored in the field.
Integer
Is the field to which the result is returned, or the format of the output value enclosed in single quotation marks.
TRIMV creates an AnV field named TITLEV by removing trailing blanks from the TITLE value. Then LENV returns the actual length of each instance of TITLEV to the ALEN field:
TABLE FILE MOVIES
PRINT
COMPUTE TITLEV/A39V = TRIMV('T', TITLE, 39, ' ', 1, TITLEV);
ALEN/I2 = LENV(TITLEV,ALEN);
BY CATEGORY NOPRINT
WHERE CATEGORY EQ 'CHILDREN'
END The output is:
TITLEV ALEN ------ ---- SMURFS, THE 11 SHAGGY DOG, THE 15 SCOOBY-DOO-A DOG IN THE RUFF 28 ALICE IN WONDERLAND 19 SESAME STREET-BEDTIME STORIES AND SONGS 39 ROMPER ROOM-ASK MISS MOLLY 26 SLEEPING BEAUTY 15 BAMBI 5
How to: |
LOCASV converts alphabetic characters to lowercase. This is similar to LOCASE, but LOCASV can return AnV output whose actual length is the lesser of the actual length of the AnV input field and an input parameter that specifies the length limit.
LOCASV(length_limit, string, outfield)
where:
Integer
Is the maximum length of the input string.
Alphanumeric of type An or AnV
Is the character string to be converted in single quotation marks, or a field or variable that contains the string. If it is a field, it can have An or AnV format. If it is a field of type AnV, its length is taken from the length bytes stored in the field. If length_limit is smaller than the actual length, the source string is truncated to this upper limit.
Alphanumeric of type An or AnV
Is the name of the field in which to store the result, or the format of the output value enclosed in single quotation marks. This value can be for a field that is AnV or An format. Is the field has AnV or An format for the returned lowercase string or the format of the output value enclosed in single quotation marks.
In this example, LOCASV converts the LAST_NAME field to lowercase and specifies a length limit of five characters. The results are stored in the LOWCV_NAME field:
TABLE FILE EMPLOYEE
PRINT LAST_NAME AND COMPUTE
LOWCV_NAME/A15V = LOCASV(5, LAST_NAME, LOWCV_NAME);
WHERE DEPARTMENT EQ 'MIS';
ENDThe output is:
LAST_NAME LOWCV_NAME --------- ---------- SMITH smith JONES jones MCCOY mccoy BLACKWOOD black GREENSPAN green CROSS cross
How to: |
The POSITV function finds the starting position of a substring within a larger string. For example, the starting position of the substring DUCT in the string PRODUCTION is 4. If the substring is not in the parent string, the function returns the value 0. This is similar to POSIT; however, the lengths of its AnV parameters are based on the actual lengths of those parameters in comparison with two other parameters that specify their sizes.
POSITV(parent, in_limit, substring, sub_limit, outfield)
where:
Alphanumeric of type An or AnV
Is the parent character string enclosed in single quotation marks, or a field or variable that contains the parent character string. If it is a field, it can have An or AnV format. If it is a field of type AnV, its length is taken from the length bytes stored in the field. If in_limit is smaller than the actual length, the source string is truncated to this upper limit.
Integer
Is the maximum length of the input field.
Alphanumeric of type An or AnV
Is the substring whose position you want to find. This can be the substring enclosed in single quotation marks, or the field that contains the string. If it is a field, it can have An or AnV format. If it is a field of type AnV, its length is taken from the length bytes stored in the field. If sub_limit is smaller than the actual length, the source string is truncated to this upper limit.
Integer
Is the maximum length of the substring.
Integer
Is the name of the field that contains the result, or the format of the output value enclosed in single quotation marks.
POSITV finds the starting position of a trailing definite or indefinite article in a movie title (such as ", THE" in SMURFS, THE). First TRIMV removes the trailing blanks from the title so that the article will be the trailing pattern:
DEFINE FILE MOVIES
TITLEV/A39V = TRIMV('T',TITLE, 39,' ', 1, TITLEV);
PSTART/I4 = POSITV(TITLEV,LENV(TITLEV,'I4'), ',', 1,'I4');
PLEN/I4 = IF PSTART NE 0 THEN LENV(TITLEV,'I4') - PSTART +1
ELSE 0;
END
TABLE FILE MOVIES
PRINT TITLE
PSTART AS 'Pattern,Start' IN 25
PLEN AS 'Pattern,Length'
BY CATEGORY NOPRINT
WHERE PLEN NE 0
ENDThe output is:
Pattern Pattern TITLE Start Length ----- ------- ------- SMURFS, THE 7 5 SHAGGY DOG, THE 11 5 MALTESE FALCON, THE 15 5 PHILADELPHIA STORY, THE 19 5 TIN DRUM, THE 9 5 FAMILY, THE 7 5 CHORUS LINE, A 12 3 MORNING AFTER, THE 14 5 BIRDS, THE 6 5 BOY AND HIS DOG, A 16 3
How to: |
The SUBSTV function extracts a substring based on where it begins and its length in the parent string. This is similar to SUBSTR; however, the end position for the string is calculated from the starting position and the substring length. Therefore, it has fewer parameters than SUBSTR. Also, the actual length of the output field if it is an AnV field is determined based on the substring length.
SUBSTV(in_limit, parent, start, sublength, outfield)
where:
Integer
Is the maximum length of the input string.
Alphanumeric of type An or AnV
Is the parent string enclosed in single quotation marks, or the field containing the parent string. If it is a field, it can have An or AnV format. If it is a field of type AnV, its length is taken from the length bytes stored in the field. If in_limit is smaller than the actual length, the source string is truncated to this size. The final length value determined by this comparison will be referred to as p_length (see the description of the outfield parameter).
Integer
Is the starting position of the substring in the parent string. The starting position can exceed the input string length.
Integer
Is the length in characters of the substring (normally end - start + 1). The end position of the substring is end =start + sublength -1. Note that the ending position can exceed the input string length depending on the provided values for start and sublength provided.
Alphanumeric of type An or AnV
Is the field to which the result is returned, or the format of the output value enclosed in single quotation marks. This field can be in An or AnV format.
If the format of outfield is AnV, the actual length, outlen, is computed as follows from the values for end, start, and p_length (see the parent parameter):
If end > p_length or end < start, then outlen = 0 otherwise, outlen = end - start + 1.
The following request extracts a trailing definite or indefinite article from a movie title (such as ", THE" in "SMURFS, THE"). First it trims the trailing blanks so that the article is the trailing pattern. Next it finds the starting position and length of the pattern. Then SUBSTV extracts the pattern and TRIMV trims the pattern from the title:
DEFINE FILE MOVIES
TITLEV/A39V = TRIMV('T',TITLE, 39,' ', 1, TITLEV);
PSTART/I4 = POSITV(TITLEV,LENV(TITLEV,'I4'), ',', 1,'I4');
PLEN/I4 = IF PSTART NE 0 THEN LENV(TITLEV,'I4') - PSTART +1
ELSE 0;
PATTERN/A20V= SUBSTV(39, TITLEV, PSTART, PLEN, PATTERN);
NEWTIT/A39V = TRIMV('T',TITLEV,39,PATTERN,LENV(PATTERN,'I4'), NEWTIT);
END
TABLE FILE MOVIES
PRINT TITLE
PSTART AS 'Pattern,Start' IN 25
PLEN AS 'Pattern,Length'
NEWTIT AS 'Trimmed,Title' IN 55
BY CATEGORY NOPRINT
WHERE PLEN NE 0
ENDThe output is:
Pattern Pattern Trimmed TITLE Start Length Title ----- ------ ------- ------- SMURFS, THE 7 5 SMURFS SHAGGY DOG, THE 11 5 SHAGGY DOG MALTESE FALCON, THE 15 5 MALTESE FALCON PHILADELPHIA STORY, THE 19 5 PHILADELPHIA STORY TIN DRUM, THE 9 5 TIN DRUM FAMILY, THE 7 5 FAMILY CHORUS LINE, A 12 3 CHORUS LINE MORNING AFTER, THE 14 5 MORNING AFTER BIRDS, THE 6 5 BIRDS BOY AND HIS DOG, A 16 3 BOY AND HIS DOG
How to: |
The TRIMV function removes leading and/or trailing occurrences of a pattern within a character string. TRIMV is similar to TRIM; however, TRIMV allows the input string and the pattern to be in AnV format.
TRIMV is useful for converting an An field to an AnV field (with the length bytes containing the actual length of the data up to the last non-blank character).
TRIMV(trim_where, string, slength_limit, pattern, plength_limit, outfield)
where:
Alphanumeric
Is one of the following, which indicates where to remove the pattern:
'L' removes leading occurrences.
'T' removes trailing occurrences.
'B' removes both leading and trailing occurrences.
Alphanumeric of type An or AnV
Is the source character string enclosed in single quotation marks, or the field containing the string. If it is a field, it can have An or AnV format. If it is a field of type AnV, its length is taken from the length bytes stored in the field. If slength_limit is smaller than the actual length, the source string is truncated to this upper limit.
Integer
Is the maximum length of the input string.
Alphanumeric of type An or AnV
Is the pattern to remove enclosed in single quotation marks. If it is a field, it can have An or AnV format. If it is a field of type AnV, its length is taken from the length bytes stored in the field. If plength_limit is smaller than the actual length, the pattern is truncated to this limit.
Integer
Is the maximum length of the pattern.
Alphanumeric of type An or AnV
Is the field to which the result is returned, or the format of the output value enclosed in single quotation marks. The field can be in AnV or An format.
If the format of outfield is AnV, the actual length is equal to the number of characters left after trimming.
TRIMV creates an AnV field named TITLEV by removing trailing blanks from the TITLE value:
TABLE FILE MOVIES
PRINT DIRECTOR
COMPUTE TITLEV/A39V = TRIMV('T', TITLE, 39, ' ', 1, TITLEV);
BY CATEGORY
ENDHere are the first 10 lines of the output:
CATEGORY DIRECTOR TITLEV
-------- -------- ------
ACTION SPIELBERG S. JAWS
VERHOVEN P. ROBOCOP
VERHOVEN P. TOTAL RECALL
SCOTT T. TOP GUN
MCDONALD P. RAMBO III
CHILDREN SMURFS, THE
BARTON C. SHAGGY DOG, THE
SCOOBY-DOO-A DOG IN THE RUFF
GEROMINI ALICE IN WONDERLAND
SESAME STREET-BEDTIME STORIES AND SONGS
How to: |
UPCASV converts alphabetic characters to uppercase like UPCASE. However, UPCASV can return AnV output whose actual length is the lesser of the actual length of the AnV input field and an input parameter that specifies the size.
UPCASV(length_limit, string, outfield)
where:
Integer
Is a positive constant or a field whose integer portion represents the size and, therefore, the upper limit for the length of the input string.
Alphanumeric of type An or AnV
Is the character string enclosed in single quotation marks, or the field containing the character string. If it is a field, it can have An or AnV format. If it is a field of type AnV, its length is taken from the length bytes stored in the field. If length_limit is smaller than the actual length, the source string is truncated to this size.
Alphanumeric of type An or AnV
Is the field to which the result is returned, or the format of the output value enclosed in single quotation marks. This can be a field with AnV or An format.
If the format of outfield is AnV, then the actual length returned is equal to the smaller of the input string length and length_limit.
Suppose you are sorting on a field that contains both uppercase and mixed-case values. The following request defines a field called LAST_NAME_MIXED that contains both uppercase and mixed-case values:
DEFINE FILE EMPLOYEE LAST_NAME_MIXED/A15=IF DEPARTMENT EQ 'MIS' THEN LAST_NAME ELSE LCWORD(15, LAST_NAME, 'A15'); LAST_NAME_UPCASV/A15V=UPCASV(5, LAST_NAME_MIXED, 'A15') ; END
Suppose you execute a request that sorts by this field:
TABLE FILE EMPLOYEE PRINT LAST_NAME_MIXED AND FIRST_NAME BY LAST_NAME_UPCASV WHERE CURR_JOBCODE EQ 'B02' OR 'A17' OR 'B04'; END
The output is:
LAST_NAME_UPCASV LAST_NAME_MIXED FIRST_NAME ---------------- --------------- ---------- BANNI Banning JOHN BLACK BLACKWOOD ROSEMARIE CROSS CROSS BARBARA MCCOY MCCOY JOHN MCKNI Mcknight ROGER ROMAN Romans ANTHONY
| Information Builders |