String Functions

Converts all the characters in stringexpr to lower case

[LOWER (stringexpr)]

Converts all the characters in stringexpr to upper case

[UPPER (stringexpr)]

Searches for the presence of the string stringexpr1 in the string stringexpr2 and returns a numeric value that indicates the position at which stringexpr1 is found in stringexpr2

[POSITION (stringexpr,stringexpr2)]

Searches stringexpr1 beginning with its nth character for the mth occurrence of stringexpr2 and returns the position of the character in stringexpr1 that is the first character of this occurrence. If n is negative, Instr counts and searches backward from the end of stringexpr1. The value of m must be positive. The default values of both n and m are 1, meaning Instr begins searching at the first character of stringexpr1 for the first occurrence of stringexpr2. The return value is relative to the beginning of stringexpr1 regardless of the value of n, and is expressed in characters. If the search is unsuccessful (if stringexpr2 does not appear m times after the nth character of stringexpr1) the return value is 0.

If n and m are not specified the function is equivalent to the ANSI SQL-92 POSITION function, except that the syntax is slightly different.

[INST (stringexpr1,stringexpr2 [,n[,m]])]

LTRIM function trims the characters specified in charset from the beginning of the string stringexpr.

[LTRIM (charset,stringexpr)]

RTRIM function trims the characters specified in charset from the end of the string stringexpr.

[RTRIM (charset,stringexpr)]

TRIM function allows you to strip the characters specified in charset from the beginning and/or the end of the string stringexpr. If charset is not specified, then blank characters would be stripped from stringexpr.

[ TRIM ({ LEADING | TRAILING | BOTH} (,charset ,stringexpr)]

 SELECT LOWER (OWNER) || '.' || LOWER (NAME)
    FROM SYSTEM.TABLE
   WHERE NAME = UPPER ('vendors');

Returns "purchdb .vendors "

 SELECT POSITION ('world', 'hello world')
    FROM SYSTEM.TABLE
   WHERE NAME = UPPER('vendors');

Returns the numeric value 7

 SELECT INSTR ('hello world hello world', 'world', 5, 2)
    FROM SYSTEM.TABLE
   WHERE NAME = UPPER('vendors');

Returns the numeric value 18 (starting position of the second occurrence of the string 'world').

 SELECT * FROM SYSTEM.TABLE
    WHERE NAME = LTRIM ('?*', 'VENDORS?*???***')
    AND OWNER = 'PURCHDB';

Returns the system table entry for PURCHDB.VENDORS

 SELECT TRIM (BOTH '?*' FROM '??**?*hello ?* world???*')
    FROM SYSTEM.TABLE
   WHERE NAME = 'VENDORS';

Returns 'hello ?* world'.

• The string functions can appear in an expression, a select list, or a search condition of an SQL data manipulation statement.

• The string functions can be applied to any string data type, including binary and long column data types.

• The string returned by the SUBSTRING function is truncated if (StartPosition + Length -1) is greater than the length of the StringExpression. Only (Length - StartPosition +1) bytes is returned, and a warning is issued.

• If Length is a simple constant, the substring returned has a maximum length equal to the value of the constant. Otherwise, the length and data type returned by the SUBSTRING function depend on the data type of StringExpression, as shown in the following table:

1. STRING_LENGTH example

   In the SELECT statement below, the PartsIllus table is searched for any row whose PartPicture contains more than 10000 bytes of data, and whose PartName is longer than 10 bytes.

      CREATE TABLE PartsIllus
                   (PartName  VARCHAR(16),
                   PartNumber  INTEGER,
                   PartPicture  LONG VARBINARY(1000000) in PartPictureSet)
                IN PartsIllusSet
      SELECT PartNumber, PartName
        FROM PartsIllus
       WHERE STRING_LENGTH(PartPicture) > 10000
         AND STRING_LENGTH(PartName)    > 10

2. SUBSTRING example

   For every row in PartsIllus, the PartNumber and the first 350 bytes of the PartPicture are inserted into the DataBank table:

      CREATE TABLE DataBank
                   (IdNumber  INTEGER,
                   Data  VARBINARY(1000))
    
      INSERT INTO DataBank
           SELECT PartNumber, SUBSTRING(PartPicture,1,350)
             FROM PartsIllus

   Display a substring of the PartPicture column in the PartsIllus table if the Data column in the DataBank table contains more than 133 bytes:

      SELECT DATA
        FROM DataBank
       WHERE STRING_LENGTH(Data) > 133