# Using eXtremeSQL String, Math and User-defined Functions

> String and math functions are often used to modify, convert or compute derived values in the result set returned from a SQL query or to assign values in a SQL `insert` or `update`. The following table lists the eXtremeSQL built-in functions:

> Name | Argument Type | Return Type | Description
> `abs(i)` | integer | integer | absolute value of the argument
> `abs(r)` | real | real | absolute value of the argument
> `sin(r)` | real | real | sin (rad)
> `cos(r)` | real | real | cos (rad)
> `tan(r)` | real | real | tan (rad)
> `asin(r)` | real | real | arcsin
> `acos(r)` | real | real | arcos
> `atan(r)` | real | real | arctan
> `exp(r)` | real | real | exponent
> `log(r)` | real | real | natural logarithm
> `ceil(r)` | real | real | the smallest integer value that is not less than r
> `floor(r)` | real | real | the largest integer value that is not less than r
> `integer(r)` | real | integer | conversion of real to integer
> `integer(s)` | string | integer | conversion of string to integer
> `length(a)` | array | integer | number of elements in array
> `length(s)` | string | integer | length of string
> `lower(s)` | string | string | lowercase string
> `real(i)` | integer | real | conversion of integer to real
> `real(s)` | string | real | conversion of string to real
> `string(i)` | integer | string | conversion of integer to string
> `string(r)` | real | string | conversion of real to string
> `substr(s,m[,n])` | string,integer[,integer] | string | substring of s, beginning at character m, n characters long (if n is omitted, to the end of string)
> `upper` | string | string | uppercase string

> The built-in functions can be called from normal SQL `select` statements as follows:

>         select string(age) from Persons;
>         select upper(name) from Persons;
>         select abs(amount) from Contributions;

> ## User-defined functions

> eXtremeSQL also allows developers to create their own user-defined functions. The user-defined function (UDF) must be defined as returning type “`static String*`” and can accept arguments of type “`Value*`”. Then it must be “registered” with the eXtremeSQL runtime by declaring a function of type “`static SqlFunctionDeclaration udf()`” with arguments specifying the type returned, the name used to call it, a pointer to the function and the number of arguments required.

> The following code snippet defines then registers a UDF:

>         // Delcare and instantiate the user-defined DateFormat function
>         static String* dateformat( McoSql::Value* date)
>         {
>             char str[64];
>             int date_val = (int)date->intValue();
>             int yy = date_val / 10000;
>             int mm = date_val / 100 % 100;
>             int dd = date_val % 100;

>             // Format dd.mm.yyyy assuming all dates are after 2000
>             sprintf( str, "%d.%d.20%02d", dd, mm, yy );
>             return String::create( str );
>         }

>         static SqlFunctionDeclaration udf
>         (
>             tpString,          // tpInt is the return value of the UDF
>             "dateformat",      // the name of the function as we’ll use it in a query
>             (void*)dateformat, // the function pointer
>             1                  // the number of arguments to the UDF
>         );

> The UDF can then be called in a normal SQL `select` statement. For example, the following statement formats the date values in the result set by calling the UDF:

>         select dateformat(date_val) from Contributions;