4. Elements of OpenSQL Statements : Functions : Scalar Functions : String Functions
 
Share this page                  
String Functions
String functions perform a variety of operations on character data. String functions can be nested. For example:
left(right(x.name, size(x.name) - 1), 3)
returns the substring of x.name from character positions 2 through 4, and
concat(concat(x.lastname, ', '), x.firstname)
concatenates x.lastname with a comma and then concatenates x.firstname with the first concatenation result. The + operator can also be used to concatenate strings:
x.lastname + ', ' + x.firstname
The following string functions do not accept long varchar or long byte columns:
• Locate
• Pad
• Shift
• Squeeze
• Trim
• Notrim
• Charextract
To apply any of the preceding functions to a long varchar or long byte column, first coerce the column to an acceptable data type. For example:
squeeze(varchar(long_varchar_column))
If a coercion function is applied to a long varchar or long byte value that is longer than 2008 characters or bytes, the result is truncated to 2008 characters or bytes.
The following table lists the string functions supported in OpenSQL. The expressions c1 and c2, representing the arguments, can be any of the string types, except where noted. The expressions len and nshift represent integer arguments.
Name
Result Type
Description
charextract(c1,n)
varchar
Returns the nth byte of c1. If n is larger than the length of the string, then the result is a blank character.
concat(c1,c2)
any character data type, byte
Concatenates one string to another. The result size is the sum of the sizes of the two arguments. If the result is a c or char string, it is padded with blanks to achieve the proper length. To determine the data type results of concatenating strings, see the table regarding results of string concatenation.
left(c1,len)
any character data type
Returns the leftmost len characters of c1. If the result is a fixed-length c or char string, it is the same length as c1, padded with blanks. The result format is the same as c1.
length(c1)
smallint
(for long varchar, returns 4-byte integer)
If c1 is a fixed-length c or char string, returns the length of c1 without trailing blanks. If c1 is a variable-length string, returns the number of characters actually in c1.
locate(c1,c2)
smallint
Returns the location of the first occurrence of c2 in c1, including trailing blanks from c2. The location is in the range 1 to size(c1). If c2 is not found, the function returns size(c1) + 1. The function size() is described below, in this table.
If c1 and c2 are different string data types, c2 is coerced into the c1 data type.
lowercase(c1)
or lower(c1)
any character or Unicode data type
Converts all upper case characters in c1 to lower case.
pad(c1)
text or varchar
Returns c1 with trailing blanks appended to c1; for instance, if c1 is a varchar string that could hold fifty characters but only has two characters, then pad(c1) appends 48 trailing blanks to c1 to form the result.
right(c1,len)
any character data type
Returns the rightmost len characters of c1. Trailing blanks are not removed first. If c1 is a fixed-length character string, the result is padded to the same length as c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1.
shift(c1,nshift)
any character data type
Shifts the string nshift places to the right if nshift > 0 and to the left if nshift < 0. If c1 is a fixed-length character string, the result is padded with blanks to the length of c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1.
size(c1)
smallint
Returns the declared size of c1 without removal of trailing blanks.
soundex(c1)
any character data type
Returns a c1 four-character field that can be used to find similar sounding strings. For example, SMITH and SMYTHE produce the same soundex code. If there are less than three characters, the result is padded by trailing zero(s). If there are more than three characters, the result is achieved by dropping the rightmost digit(s).
This function is useful for finding like-sounding strings quickly. A list of similar sounding strings can be shown in a search list rather than just the next strings in the index.
squeeze(c1)
text or varchar
Compresses white space. White space is defined as any sequence of blanks, null characters, newlines (line feeds), carriage returns, horizontal tabs and form feeds (vertical tabs). Trims white space from the beginning and end of the string, and replaces all other white space with single blanks.
This function is useful for comparisons. The value for c1 must be a string of variable‑length character string data type (not fixed-length character data type). The result is the same length as the argument.
substring(c1 from n1 [for n2])
varchar or nvarchar
Returns a substring of parameter c1 starting at offset n1. If n2 is specified, the resulting string is min(n2, length(c1)-n1) in length. If n1 is 0 or negative, the resulting substring starts with the 1st byte of c1. If n1 > length(c1), the resulting string has length 0. If n2 is negative, an error is returned.
trim(c1)
text or varchar
Returns c1 without trailing blanks. The result has the same length as c1.
notrim(c1)
any character string variable
Retains trailing blanks when placing a value in a varchar column. This function can only be used in an embedded OpenSQL program. For more information, see the Embedded SQL Companion Guide.
uppercase(c1)
or upper(c1)
any character or Unicode data type
Converts all lower case characters in c1 to upper case.