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:
Available Operating Systems: All Available Languages: reporting | How to: | Example: |
LENV returns the actual length of an AnV field or the size of an An field.
LENV(string, outfield)
where:
string
Alphanumeric
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.
outfield
Is the name of the field that contains the result, or the format of the output value enclosed in single quotation marks.
LENV creates an AnV field named TITLEV by removing trailing blanks from the TITLE value and returns the actual length of each instance of TITLEV to the ALEN field:
TABLE FILE MOVIES
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
Available Operating Systems: All Available Languages: reporting | How to: | Example: |
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:
length_limit
Numeric
Is the maximum length of the input string.
string
Alphanumeric
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.
outfield
Alphanumeric
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/A15 = LOCASV(5, LAST_NAME, LOWCV_NAME);
WHERE DEPARTMENT EQ 'MIS';
END
The output is:
LAST_NAME LOWCV_NAME
--------- ----------
SMITH smith
JONES jones
MCCOY mccoy
BLACKWOOD black
GREENSPAN green
CROSS cross
Available Operating Systems: All Available Languages: reporting | How to: | Example: |
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:
parent
Alphanumeric
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.
in_limit
Integer
Is the maximum length of the input field.
substring
Alphanumeric
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.
sub_limit
Numeric
Is the maximum length of the substring.
outfield
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
END
The 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
Available Operating Systems: All Available Languages: reporting | How to: | Example: |
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:
in_limit
Numeric
Is the maximum length of the input string.
parent
Alphanumeric
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).
start
Integer
Is the starting position of the substring in the parent string. The starting position can exceed the input string length.
sublength
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.
outfield
Alphanumeric
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
END
The 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
Available Operating Systems: All Available Languages: reporting | How to: | Example: |
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:
trim_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.
string
Alphanumeric
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.
slength_limit
Integer
Is the maximum length of the input string.
pattern
Alphanumeric
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.
plength_limit
Integer
Is the maximum length of the pattern.
outfield
Alphanumeric
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
END
Here 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
...
Available Operating Systems: All Available Languages: reporting | How to: | Example: |
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:
length_limit
Numeric
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.
string
Alphanumeric
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.
outfield
Alphanumeric
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/A15=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
-RUN
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