STRING (SQL)

A function that converts and concatenates expressions into a string.

Synopsis

STRING(string1[,string2][,...][,stringN])

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.

Arguments

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.

Examples

In the following example, STRING concatenates three substrings into a single string. The example shows the handling of blank spaces, the empty string, and NULL:

SELECT STRING('a','b','c'), 
   STRING('a',' ','c'), 
   STRING('a','','c'), 
   STRING('a',NULL,'c')

In the following example, STRING converts numerics into a string. All of these STRING functions return the string '123':

SELECT STRING(123),
   STRING(+00123.00),
   STRING('1',23),
   STRING(1,(10*2)+3)

In the following example, STRING retrieves sample data from fields and concatenates it into a string:

SELECT STRING(Name,Age)
FROM Sample.Person