# QBFunctions

## Overview

The `QBFunctions` class provides a comprehensive set of SQL functions designed to enhance query building in `QBSelect`. It enables the creation of mathematical, string, and date-based expressions, along with conditional and custom logic.

This class allows users to perform operations like calculating absolute values, rounding, and extracting substrings. It also includes advanced capabilities such as casting data types, concatenating strings, trimming whitespace, and formatting dates. With access to aggregation methods and support for custom function definitions, the class is versatile in handling diverse SQL requirements.

For additional guidance on query construction and execution, refer to the [QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect) section of the documentation.

## Properties Summarized

| Type                                                                                       | Name              | Summary                                                                      |
| ------------------------------------------------------------------------------------------ | ----------------- | ---------------------------------------------------------------------------- |
| [QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect) | [parent](#parent) | Get query builder parent table clause, this may be a query or a join clause. |
| [QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect) | [root](#root)     | Get query builder parent.                                                    |

## Methods Summarized

| Type                                                                                                     | Name                                                                                                                      | Summary                               |
| -------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------- | ------------------------------------- |
| [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn)   | [abs(value)](#abs-value)                                                                                                  | Create abs(column) expression         |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [bit\_length(value)](#bit_length-value)                                                                                   | Create bit\_length(column) expression |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [cardinality(arg)](#cardinality-arg)                                                                                      | Create cardinality(column) expression |
| [QBGenericColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbgenericcolumn) | [cast(value, type)](#cast-value-type)                                                                                     | Create cast(column, type) expression  |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [ceil(arg)](#ceil-arg)                                                                                                    | Create ceil(column) expression        |
| [QBGenericColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbgenericcolumn) | [coalesce(args)](#coalesce-args)                                                                                          | Create coalesce(arg) expression       |
| [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn)       | [concat(arg1, arg2)](#concat-arg1-arg2)                                                                                   | Concatenate with value                |
| [QBGenericColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbgenericcolumn) | [custom(name, args)](#custom-name-args)                                                                                   | Call a custom defined function.       |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [day(arg)](#day-arg)                                                                                                      | Extract day from date                 |
| [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn)   | [divide(arg1, arg2)](#divide-arg1-arg2)                                                                                   | Divide by value                       |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [floor(arg)](#floor-arg)                                                                                                  | Create floor(column) expression       |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [hour(arg)](#hour-arg)                                                                                                    | Extract hour from date                |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [len(value)](#len-value)                                                                                                  | Create length(column) expression      |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [locate(string1, string2)](#locate-string1-string2)                                                                       | Create locate(arg) expression         |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [locate(string1, string2, start)](#locate-string1-string2-start)                                                          | Create locate(arg, start) expression  |
| [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn)       | [lower(value)](#lower-value)                                                                                              | Create lower(column) expression       |
| [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn)   | [minus(arg1, arg2)](#minus-arg1-arg2)                                                                                     | Subtract value                        |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [minute(arg)](#minute-arg)                                                                                                | Extract minute from date              |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [mod(dividend, divisor)](#mod-dividend-divisor)                                                                           | Create mod(arg) expression            |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [month(arg)](#month-arg)                                                                                                  | Extract month from date               |
| [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn)   | [multiply(arg1, arg2)](#multiply-arg1-arg2)                                                                               | Multiply with value                   |
| [QBGenericColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbgenericcolumn) | [nullif(arg1, arg2)](#nullif-arg1-arg2)                                                                                   | Create nullif(arg) expression         |
| [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn)   | [plus(arg1, arg2)](#plus-arg1-arg2)                                                                                       | Add up value                          |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [round(arg)](#round-arg)                                                                                                  | Create round(column) expression       |
| [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn)   | [round(arg, decimals)](#round-arg-decimals)                                                                               | Create round(column) expression       |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [second(arg)](#second-arg)                                                                                                | Extract second from date              |
| [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn)   | [sqrt(value)](#sqrt-value)                                                                                                | Create sqrt(column) expression        |
| [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn)       | [substring(arg, pos)](#substring-arg-pos)                                                                                 | Create substring(pos) expression      |
| [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn)       | [substring(arg, pos, len)](#substring-arg-pos-len)                                                                        | Create substring(pos, len) expression |
| [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn)       | [trim(value)](#trim-value)                                                                                                | Create trim(column) expression        |
| [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn)       | [trim(leading\_trailing\_both, characters, fromKeyword, value)](#trim-leading_trailing_both-characters-fromkeyword-value) | Create trim(column) expression        |
| [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn)       | [upper(value)](#upper-value)                                                                                              | Create upper(column) expression       |
| [QBScoreColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbscorecolumn)     | [vector\_distance(arg, embedding)](#vector_distance-arg-embedding)                                                        |                                       |
| [QBScoreColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbscorecolumn)     | [vector\_score(arg, embedding)](#vector_score-arg-embedding)                                                              |                                       |
| [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) | [year(arg)](#year-arg)                                                                                                    | Extract year from date                |

## Properties Detailed

### parent

Get query builder parent table clause, this may be a query or a join clause.

**Type**\
[QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect)

**Sample**

```js
var query = datasources.db.example_data.person.createSelect();
	query.where.add(query.joins.person_to_parent.joins.person_to_parent.columns.name.eq('john'))
	foundset.loadRecords(query)
```

### root

Get query builder parent.

**Type**\
[QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect)

**Sample**

```js
var subquery = datasources.db.example_data.order_details.createSelect();

	var query = datasources.db.example_data.orders.createSelect();
	query.where.add(query
		.or
			.add(query.columns.order_id.not.isin([1, 2, 3]))

			.add(query.exists(
					subquery.where.add(subquery.columns.orderid.eq(query.columns.order_id)).root
			))
		)

	foundset.loadRecords(query)
```

## Methods Detailed

### abs(value)

Create abs(column) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn) A query builder column representing the absolute value of the input.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynum.abs.eq(query.functions.abs(myval)))
foundset.loadRecords(query);
```

### bit\_length(value)

Create bit\_length(column) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the bit length of the value.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.bit_length.eq(query.functions.bit_length('Sample')))
foundset.loadRecords(query);
```

### cardinality(arg)

Create cardinality(column) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** date object

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the year component of a date/time value.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.year.eq(query.functions.year(mydatevar))
foundset.loadRecords(query);
```

### cast(value, type)

Create cast(column, type) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** object to cast
* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **type** type see QUERY\_COLUMN\_TYPES

**Returns:** [QBGenericColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbgenericcolumn) A query builder column representing the value cast to the specified type.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.result.add(query.functions.cast("22",QUERY_COLUMN_TYPES.TYPE_INTEGER)).add(query.columns.amt_discount.cast(QUERY_COLUMN_TYPES.TYPE_STRING));
application.output(databaseManager.getDataSetByQuery(query,1).getAsHTML())
```

### ceil(arg)

Create ceil(column) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** number object

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the smallest integer greater than or equal to the input.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.ceil.eq(query.functions.ceil(myvar))
foundset.loadRecords(query);
```

### coalesce(args)

Create coalesce(arg) expression

**Parameters**

* [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **args** arguments to coalesce

**Returns:** [QBGenericColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbgenericcolumn) A query builder column that returns the first non-null argument.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mycol.coalesce('defval').eq(query.functions.coalesce(myvar, 'defval'))
foundset.loadRecords(query);
```

### concat(arg1, arg2)

Concatenate with value

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg1** ;
* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg2** ;

**Returns:** [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn) A query builder column representing the concatenation of two arguments.

**Sample**

```js
var query = datasources.db.udm.contacts.createSelect();
	query.result.add(query.columns.name_first.concat(' ').concat(query.columns.name_last))
	var ds = databaseManager.getDataSetByQuery(query, -1)
```

### custom(name, args)

Call a custom defined function.

**Parameters**

* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **name** custom function name
* [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **args** function arguments

**Returns:** [QBGenericColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbgenericcolumn) A query builder column representing a custom function with the given name and arguments.

**Sample**

```js
// select myadd(freight, 500) from orders
	var query = datasources.db.example_data.orders.createSelect();
	query.result.add(query.functions.custom('myadd', query.columns.freight, 500));
	var dataset = databaseManager.getDataSetByQuery(query, 100);
```

### day(arg)

Extract day from date

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** date object

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the day component of a date/time value.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.day.eq(query.functions.day(mydatevar))
foundset.loadRecords(query);
```

### divide(arg1, arg2)

Divide by value

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg1** ;
* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg2** ;

**Returns:** [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn) A query builder column representing the division of two arguments.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.divide(2).eq(query.functions.divide(myvar, 2))
foundset.loadRecords(query);
```

### floor(arg)

Create floor(column) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** number object

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the largest integer less than or equal to the input.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.floor.eq(query.functions.floor(myvar))
foundset.loadRecords(query);
```

### hour(arg)

Extract hour from date

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** date object

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the hour component of a date/time value.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.hour.eq(query.functions.hour(mydatevar))
foundset.loadRecords(query);
```

### len(value)

Create length(column) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the length of the value.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.len.eq(query.functions.len('Sample')))
foundset.loadRecords(query);
```

### locate(string1, string2)

Create locate(arg) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **string1** string to locate
* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **string2** string to search in

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the position of the first occurrence of one string in another.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.locate('amp').eq(query.functions.locate('amp', 'Sample')))
foundset.loadRecords(query);
```

### locate(string1, string2, start)

Create locate(arg, start) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **string1** string to locate
* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **string2** string to search in
* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **start** start pos

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the position of the first occurrence of one string in another, starting from a given position.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.locate('amp', 1).eq(query.functions.locate('amp', 'Sample', 1)))
foundset.loadRecords(query);
```

### lower(value)

Create lower(column) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn) A query builder column representing the value converted to lowercase.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.lower.eq(query.functions.lower('Sample')))
foundset.loadRecords(query);
```

### minus(arg1, arg2)

Subtract value

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg1** ;
* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg2** ;

**Returns:** [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn) A query builder column representing the difference between two arguments.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.minus(2).eq(query.functions.minus(myvar, 2))
foundset.loadRecords(query);
```

### minute(arg)

Extract minute from date

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** date object

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the minute component of a date/time value.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.minute.eq(query.functions.minute(mydatevar))
foundset.loadRecords(query);
```

### mod(dividend, divisor)

Create mod(arg) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **dividend** ;
* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **divisor** ;

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the remainder of the division of two numbers.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.mod(2).eq(query.functions.mod(myvar, 2))
foundset.loadRecords(query);
```

### month(arg)

Extract month from date

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** date object

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the month component of a date/time value.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.month.eq(query.functions.month(mydatevar))
foundset.loadRecords(query);
```

### multiply(arg1, arg2)

Multiply with value

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg1** ;
* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg2** ;

**Returns:** [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn) A query builder column representing the product of two arguments.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.multiply(2).eq(query.functions.multiply(myvar, 2))
foundset.loadRecords(query);
```

### nullif(arg1, arg2)

Create nullif(arg) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg1** ;
* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg2** ;

**Returns:** [QBGenericColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbgenericcolumn) A query builder column that returns null if the two arguments are equal.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.nullif('none').eq(query.functions.nullif('Sample', 'none')))
foundset.loadRecords(query);
```

### plus(arg1, arg2)

Add up value

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg1** ;
* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg2** ;

**Returns:** [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn) A query builder column representing the sum of two arguments.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.plus(2).eq(query.functions.plus(myvar, 2))
foundset.loadRecords(query);
```

### round(arg)

Create round(column) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** number object

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the input rounded to the nearest integer.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.round.eq(query.functions.round(myvar))
foundset.loadRecords(query);
```

### round(arg, decimals)

Create round(column) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** number object
* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **decimals** The number of decimal places to round number to, default 0

**Returns:** [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn) A query builder column representing the input rounded to the specified number of decimal places.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynumcol.round.eq(query.functions.round(myvar, 1))
foundset.loadRecords(query);
```

### second(arg)

Extract second from date

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** date object

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the second component of a date/time value.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.second.eq(query.functions.second(mydatevar))
foundset.loadRecords(query);
```

### sqrt(value)

Create sqrt(column) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBNumberColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbnumbercolumn) A query builder column representing the square root of the input.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mynum.sqrt.eq(query.functions.sqrt(myval)))
foundset.loadRecords(query);
```

### substring(arg, pos)

Create substring(pos) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** column name
* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **pos** position

**Returns:** [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn) A query builder column representing a substring starting at the specified position.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.substring(3).eq(query.functions.substring('Sample', 3)))
foundset.loadRecords(query);
```

### substring(arg, pos, len)

Create substring(pos, len) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** column name
* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **pos** position
* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **len** length

**Returns:** [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn) A query builder column representing a substring of specified length starting at the specified position.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.substring(3, 2).eq(query.functions.substring('Sample', 3, 2)))
foundset.loadRecords(query);
```

### trim(value)

Create trim(column) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn) A query builder column representing the value with leading and trailing spaces removed.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.trim.eq(query.functions.trim('Sample')))
foundset.loadRecords(query);
```

### trim(leading\_trailing\_both, characters, fromKeyword, value)

Create trim(column) expression

**Parameters**

* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **leading\_trailing\_both** 'leading', 'trailing' or 'both'
* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **characters** characters to remove
* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **fromKeyword** 'from'
* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** value to trim

**Returns:** [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn) A query builder column representing the value with specified characters trimmed from a specified position.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
// show shipname but remove trailing space
query.result.add(query.functions.trim('trailing', ' ', 'from', query.columns.shipname));
foundset.loadRecords(query);
```

### upper(value)

Create upper(column) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn) A query builder column representing the value converted to uppercase.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.upper.eq(query.functions.upper('Sample')))
foundset.loadRecords(query);
```

### vector\_distance(arg, embedding)

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** vector column
* [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **embedding** embedding object

**Returns:** [QBScoreColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbscorecolumn) A query builder column representing the database native cosine distance of the vector column compared with the embedding.

**Sample**

```js
var query = datasources.db.example_data.books.createSelect();
query.sort.add(query.joins.books_to_books_embeddings.columns.embedding.vector_distance(model.embedding('Magic or Fantasy')))
foundset.loadRecords(query);
```

### vector\_score(arg, embedding)

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** vector column
* [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **embedding** embedding object

**Returns:** [QBScoreColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbscorecolumn) A query builder column representing the normalized vector score of the vector column compared with the embedding.

**Sample**

```js
var query = datasources.db.example_data.books.createSelect();
query.where.add(query.joins.books_to_books_embeddings.columns.embedding.vector_score(model.embedding('Magic or Fantasy')).min_score(0.7))
foundset.loadRecords(query);
```

### year(arg)

Extract year from date

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** date object

**Returns:** [QBIntegerColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbintegercolumn) A query builder column representing the year component of a date/time value.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.mydatecol.year.eq(query.functions.year(mydatevar))
foundset.loadRecords(query);
```

***