STRING (SQL)
Synopsis
STRING(string1[,string2][,...][,stringN])
Arguments
| Argument | Description |
|---|---|
| string | An expression, which can be a field name, a string literal, a numeric, or the result of another function, where the underlying data type can be represented as any character type (such as CHAR or VARCHAR). If a field name is specified, the logical value is used. |
Description
STRING converts one or more strings to the STRING format, and then concatenates these strings into a single string. No case transformation is performed.
STRING converts numerics to their canonical form before string conversion. It therefore performs arithmetic operations, removes leading and trailing zeros and leading plus signs from numbers.
If any of the string arguments is NULL or the empty string (''), STRING concatenates all other arguments and removes NULL and the empty string from the concatenation. If all of the string arguments are NULL, STRING returns NULL. If all of the string arguments are the empty string (''), STRING returns the empty string. STRING retains whitespace.
You can use the %SQLSTRING function to convert a data value for case-sensitive string comparison, or the %SQLUPPER function to convert a data value for not case-sensitive string comparison.
Examples
In the following Embedded SQL example, STRING concatenates three substrings into a single string. The example shows the handling of blank spaces, the empty string, and NULL:
&sql(SELECT STRING('a','b','c'),
STRING('a',' ','c'),
STRING('a','','c'),
STRING('a',NULL,'c')
INTO :w,:x,:y,:z)
IF SQLCODE'=0 {
WRITE !,"Error code ",SQLCODE }
ELSE {
WRITE !,"Resulting string is:",w // w = "abc"
WRITE !,"Resulting string is:",x // x = "a c"
WRITE !,"Resulting string is:",y // y = "ac"
WRITE !,"Resulting string is:",z // z = "ac"
}
In the following Embedded SQL example, STRING converts numerics into a string. All of these STRING functions return the string '123':
&sql(SELECT STRING(123),
STRING(+00123.00),
STRING('1',23),
STRING(1,(10*2)+3)
INTO :w,:x,:y,:z)
IF SQLCODE'=0 {
WRITE !,"Error code ",SQLCODE }
ELSE {
WRITE !,"Resulting string is:",w // w = 123
WRITE !,"Resulting string is:",x // x = 123
WRITE !,"Resulting string is:",y // y = 123
WRITE !,"Resulting string is:",z // z = 123}
In the following Embedded SQL example, STRING retrieves sample data from fields and concatenates it into a string:
&sql(SELECT STRING(Name,Age)
INTO :x
FROM Sample.Person)
IF SQLCODE'=0 {
WRITE !,"Error code ",SQLCODE }
ELSE {
WRITE !,"Resulting string is:",x }
