Simplified Character Functions

In this section:

 

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:


Top of page

x
CHAR_LENGTH: Returning the Length in Characters of a String

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.



x
Syntax: How to Return the Length of a String in Characters
CHAR_LENGTH(source_string)

where:

source_string

Alphanumeric

Is the string whose length is returned.

The data type of the returned length value is Integer.



Example: Returning the Length of a String

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

Top of page

x
DIGITS: Converting a Number to a Character String

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.



x
Syntax: How to Convert a Number to a Character String
DIGITS(number,length)

where:

number

Integer

Is the number to be converted, stored in a field with data type Integer.

length

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.



Example: Converting a Number to a Character String

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:



x
Reference: Usage Notes for DIGITS

Top of page

x
LPAD: Left-Padding a Character String

How to:

Reference:

LPAD uses a specified character and output length to return a character string padded on the left with that character.



x
Syntax: How to Pad a Character String on the Left
LPAD(string, out_length, pad_character) 

where:

string

Fixed length alphanumeric

Is a string to pad on the left side.

out_length

Integer

Is the length of the output string after padding.

pad_character

Fixed length alphanumeric

Is a single character to use for padding.



Example: Left-Padding a String

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:



x
Reference: Usage Notes for LPAD

Top of page

x
LOWER: Returning a String With All Letters Lowercase

How to:

The LOWER function takes a source string and returns a string of the same data type with all letters translated to lowercase.



x
Syntax: How to Return a String With All Letters Lowercase
LOWER(source_string)

where:

source_string

Alphanumeric

Is the string to convert to lowercase.

The returned string is the same data type and length as the source string.



Example: Converting a String to Lowercase

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    

Top of page

x
LTRIM: Removing Blanks From the Left End of a String

How to:

The LTRIM function removes all blanks from the left end of a string.



x
Syntax: How to Remove Blanks From the Left End of a String
LTRIM(source_string)

where:

source_string

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.



Example: Removing Blanks From the Left End of a 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. 

Top of page

x
POSITION: Returning the First Position of a Substring in a Source String

How to:

The POSITION function returns the first position (in characters) of a substring in a source string.



x
Syntax: How to Return the First Position of a Substring in a Source String
POSITION(pattern, source_string)

where:

pattern

Alphanumeric

Is the substring whose position you want to locate. The string can be as short as a single character, including a single blank.

source_string

Alphanumeric

Is the string in which to find the pattern.

The data type of the returned value is Integer.



Example: Returning the First Position of a Substring

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

Top of page

x
RTRIM: Removing Blanks From the Right End of a String

How to:

The RTRIM function removes all blanks from the right end of a string.



x
Syntax: How to Remove Blanks From the Right End of a String
RTRIM(source_string)

where:

source_string

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.



Example: Removing Blanks From the Right End of a 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./     

Top of page

x
SUBSTRING: Extracting a Substring From a Source String

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.



x
Syntax: How to Extract a Substring From a Source String
SUBSTRING(source_string, start_position, length_limit)

where:

source_string

Alphanumeric

Is the string from which to extract the substring. It can be a field, a literal in single quotation marks ('), or a variable.

start_position

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.

length_limit

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.



Example: Extracting a Substring From a Source String

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    

Top of page

x
RPAD: Right-Padding a Character String

How to:

Reference:

RPAD uses a specified character and output length to return a character string padded on the right with that character.



x
Syntax: How to Pad a Character String on the Right
RPAD(string, out_length, pad_character) 

where:

string

Alphanumeric

Is a string to pad on the right side.

out_length

Integer

Is the length of the output string after padding.

pad_character

Alphanumeric

Is a single character to use for padding.



Example: Right-Padding a String

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:



x
Reference: Usage Notes for RPAD

Top of page

x
TOKEN: Extracting a Token From a String

How to:

The TOKEN function extracts a token (substring) based on a token number and a delimiter character.



x
Syntax: How to Extract a Token From a String
TOKEN(string, delimiter, number)

where:

string

Fixed length alphanumeric

Is the character string from which to extract the token.

delimiter

Fixed length alphanumeric

Is a single character delimiter.

number

Integer

Is the token number to extract.



Example: Extracting a Token From a String

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:


Top of page

x
TRIM_: Removing Leading Characters, Trailing Characters, or Both From a String

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.



x
Syntax: How to Remove Leading Characters, Trailing Characters, or Both From a String
TRIM_(trim_where, trim_character, source_string) 

where:

trim_where

Keyword

Defines where to trim the source string. Valid values are:

  • LEADING, which removes leading occurrences.
  • TRAILING, which removes trailing occurrences.
  • BOTH, which removes leading and trailing occurrences.
trim_character

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 (‘ ‘).

source_string

Alphanumeric

Is the string to be trimmed.

The data type of the returned string is AnV.



Example: Trimming a Character From a String

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.       

Top of page

x
UPPER: Returning a String With All Letters Uppercase

How to:

The UPPER function takes a source string and returns a string of the same data type with all letters translated to uppercase.



x
Syntax: How to Return a String With All Letters Uppercase
UPPER(source_string)

where:

source_string

Alphanumeric

Is the string to convert to uppercase.

The returned string is the same data type and length as the source string.



Example: Converting Letters to Uppercase

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