---
title: "Feature: #103309 - Add more expression methods to ExpressionBuilder"
manual: "TYPO3 Core Changelog"
version: "main"
permalink: "https://docs.typo3.org/permalink/changelog:feature-103309-1709741435"
source: "Changelog/13.1/Feature-103309-AddMoreExpressionMethodsToExpressionBuilder.rst"
modified: "2026-09-15T10:46:36+00:00"
---

# Feature: #103309 - Add more expression methods to ExpressionBuilder

See [forge#103309](https://forge.typo3.org/issues/103309)

## Description

The TYPO3 `\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder`
provides a relatively conservative set of database query expressions since a
couple of TYPO3 and Doctrine DBAL versions now.

Additional expression methods are now available to build more advanced database
queries that ensure compatibility across supported database vendors.

-   [ExpressionBuilder::as()](https://docs.typo3.org/permalink/changelog:php-expressionbuilder-as)
-   [ExpressionBuilder::concat()](https://docs.typo3.org/permalink/changelog:php-expressionbuilder-concat)
-   [ExpressionBuilder::castVarchar()](https://docs.typo3.org/permalink/changelog:php-expressionbuilder-castvarchar)
-   [ExpressionBuilder::castInt()](https://docs.typo3.org/permalink/changelog:php-expressionbuilder-castint)
-   [ExpressionBuilder::repeat()](https://docs.typo3.org/permalink/changelog:php-expressionbuilder-repeat)
-   [ExpressionBuilder::space()](https://docs.typo3.org/permalink/changelog:php-expressionbuilder-space)
-   [ExpressionBuilder::left()](https://docs.typo3.org/permalink/changelog:php-expressionbuilder-left)
-   [ExpressionBuilder::right()](https://docs.typo3.org/permalink/changelog:php-expressionbuilder-right)
-   [ExpressionBuilder::leftPad()](https://docs.typo3.org/permalink/changelog:php-expressionbuilder-leftpad)
-   [ExpressionBuilder::rightPad()](https://docs.typo3.org/permalink/changelog:php-expressionbuilder-rightpad)

### `ExpressionBuilder::as()`

Creates a statement to append a field alias to a value, identifier or sub-expression.

> [!NOTE]
> Some `ExpressionBuilder` methods provides a argument to directly add
> the expression alias to reduce some nesting. This method can be used for
> custom expressions and avoids recurring conditional quoting and alias appending.

**Method signature**

```php
/**
 * @param string $expression Value, identifier or expression which
 *                           should be aliased.
 * @param string $asIdentifier Used to add a field identifier alias
 *                             (`AS`) if non-empty string (optional).
 *
 * @return string   Returns aliased expression.
 */
public function as(
    string $expression,
    string $asIdentifier = '',
): string {}

// use TYPO3\CMS\Core\Database\Connection;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');
$expressionBuilder = $queryBuilder->expr();

$queryBuilder->selectLiteral(
  $queryBuilder->quoteIdentifier('uid'),
  $expressionBuilder->as('(1 + 1 + 1)', 'calculated_field'),
);

$queryBuilder->selectLiteral(
  $queryBuilder->quoteIdentifier('uid'),
  $expressionBuilder->as(
    $expressionBuilder->concat(
        $expressionBuilder->literal('1'),
        $expressionBuilder->literal(' '),
        $expressionBuilder->literal('1'),
    ),
    'concatenated_value'
  ),
);
```

### `ExpressionBuilder::concat()`

Can be used to concatenate values, row field values or expression results into
a single string value.

> [!NOTE]
> The created expression is built on the proper platform specific and preferred
> concatenation method, for example `string || string || string || ...`
> for SQLite and `CONCAT(...string)` for other database vendors.

**Method signature**

```php
/**
 * @param string ...$parts      Unquoted value or expression parts to
 *                              concatenate with each other
 * @return string  Returns the concatenation expression compatible with
 *                 the database connection platform.
 */
public function concat(string ...$parts): string {}
```

**Usage example**

```php
// use TYPO3\CMS\Core\Database\Connection;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('pages');
$expressionBuilder = $queryBuilder->expr();
$result = $queryBuilder
    ->select('uid', 'pid', 'title')
    ->addSelectLiteral(
        $expressionBuilder->concat(
            $queryBuilder->quoteIdentifier('title'),
            $queryBuilder->quote(' - ['),
            $queryBuilder->quoteIdentifier('uid'),
            $queryBuilder->quote('|'),
            $queryBuilder->quoteIdentifier('pid'),
            $queryBuilder->quote(']'),
        ) . ' AS ' . $queryBuilder->quoteIdentifier('page_title_info')
    )
    ->where(
        $expressionBuilder->eq(
            'pid',
            $queryBuilder->createNamedParameter(0, Connection::PARAM_INT)
        ),
    )
    ->executeQuery();

while ($row = $result->fetchAssociative()) {
    // $row = array{
    //  'uid' => 1,
    //  'pid' => 0,
    //  'title' => 'Site Root Page',
    //  'page_title_info' => 'Site Root Page - [1|0]',
    // }
}
```

> [!WARNING]
> Be aware to properly quote values, identifiers and sub-expressions.
> No automatic quoting will be applied.

### `ExpressionBuilder::castVarchar()`

Can be used to create an expression which converts a value, row field value or
the result of an expression to varchar type with dynamic length.

> [!NOTE]
> Use the platform specific preferred way for casting to dynamic length
> character type, which means `CAST("value" AS VARCHAR(<LENGTH>))`
> or `CAST("value" AS CHAR(<LENGTH>))` is used, except PostgreSQL.
> For PostgreSQL the `"value"::INTEGER` cast notation is used.

**Method signature**

```php
/**
 * @param string    $value          Unquoted value or expression,
 *                                  which should be casted.
 * @param int       $length         Dynamic varchar field length.
 * @param string    $asIdentifier   Used to add a field identifier alias
 *                                  (`AS`) if non-empty string (optional).
 * @return string   Returns the cast expression compatible for the database platform.
 */
public function castVarchar(
    string $value,
    int $length = 255,
    string $asIdentifier = '',
): string {}
```

**Usage example**

```php
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$fieldVarcharCastExpression = $queryBuilder->expr()->castVarchar(
    $queryBuilder->quote('123'), // integer as string
    255,                         // convert to varchar(255) field - dynamic length
    'new_field_identifier',
);

$fieldExpressionCastExpression = $queryBuilder->expr()->castVarchar(
    '(100 + 200)',           // calculate a integer value
    100,                     // dynamic varchar(100) field
    'new_field_identifier',
);
```

> [!WARNING]
> Be aware to properly quote values, identifiers and sub-expressions.
> No automatic quoting will be applied.

### `ExpressionBuilder::castInt()`

Can be used to create an expression which converts a value, row field value or
the result of an expression to signed integer type.

> [!NOTE]
> Use the platform specific preferred way for casting to dynamic length
> character type, which means `CAST("value" AS INTEGER)` for most database vendors
> except PostgreSQL. For PostgreSQL the `"value"::INTEGER` cast notation
> is used.

**Method signature**

```php
/**
 * @param string    $value         Quoted value or expression result which
 *                                 should be casted to integer type.
 * @param string    $asIdentifier  Used to add a field identifier alias
 *                                 (`AS`) if non-empty string (optional).
 * @return string   Returns the integer cast expression compatible with the
 *                  connection database platform.
 */
public function castInt(string $value, string $asIdentifier = ''): string {}
```

**Usage example**

```php
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('pages');
$queryBuilder
    ->select('uid')
    ->from('pages');

// simple value (quoted) to be used as sub-expression
$expression1 = $queryBuilder->expr()->castInt(
    $queryBuilder->quote('123'),
);

// simple value (quoted) to return as select field
$queryBuilder->addSelectLiteral(
    $queryBuilder->expr()->castInt(
        $queryBuilder->quote('123'),
        'virtual_field',
    ),
);

$expression3 = queryBuilder->expr()->castInt(
  $queryBuilder->quoteIdentifier('uid'),
);

// expression to be used as sub-expression
$expression4 = $queryBuilder->expr()->castInt(
    $queryBuilder->expr()->castVarchar('(1 * 10)'),
);

// expression to return as select field
$queryBuilder->addSelectLiteral(
    $queryBuilder->expr()->castInt(
        $queryBuilder->expr()->castVarchar('(1 * 10)'),
        'virtual_field',
    ),
);
```

> [!WARNING]
> Be aware to properly quote values, identifiers and sub-expressions.
> No automatic quoting will be applied.

### `ExpressionBuilder::repeat()`

Create a statement to generate a value repeating defined `$value` for
`$numberOfRepeats` times. This method can be used to provide the
repeat number as a sub-expression or calculation.

> [!NOTE]
> `REPEAT(string, number)` is used to build this expression for all database
> vendors except SQLite for which the compatible replacement construct expression
> `REPLACE(PRINTF('%.' || <valueOrStatement> || 'c', '/'),'/', <repeatValue>)`
> is used, based on `REPLACE()` and the built-in `printf()`.

**Method signature**

```php
/**
 * @param int|string    $numberOfRepeats    Statement or value defining
 *                                          how often the $value should
 *                                          be repeated. Proper quoting
 *                                          must be ensured.
 * @param string        $value              Value which should be repeated.
 *                                          Proper quoting must be ensured.
 * @param string        $asIdentifier       Provide `AS` identifier if not
 *                                          empty.
 * @return string   Returns the platform compatible statement to create the
 *                  x-times repeated value.
 */
public function repeat(
    int|string $numberOfRepeats,
    string $value,
    string $asIdentifier = '',
): string {}
```

**Usage example**

```php
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$expression1 = $queryBuilder->expr()->repeat(
    10,
    $queryBuilder->quote('.'),
);

$expression2 = $queryBuilder->expr()->repeat(
    20,
    $queryBuilder->quote('0'),
    $queryBuilder->quoteIdentifier('aliased_field'),
);

$expression3 = $queryBuilder->expr()->repeat(
    20,
    $queryBuilder->quoteIdentifier('table_field'),
    $queryBuilder->quoteIdentifier('aliased_field'),
);

$expression4 = $queryBuilder->expr()->repeat(
    $queryBuilder->expr()->castInt(
        $queryBuilder->quoteIdentifier('repeat_count_field')
    ),
    $queryBuilder->quoteIdentifier('table_field'),
    $queryBuilder->quoteIdentifier('aliased_field'),
);

$expression5 = $queryBuilder->expr()->repeat(
    '(7 + 3)',
    $queryBuilder->quote('.'),
);

$expression6 = $queryBuilder->expr()->repeat(
  '(7 + 3)',
  $queryBuilder->concat(
    $queryBuilder->quote(''),
    $queryBuilder->quote('.'),
    $queryBuilder->quote(''),
  ),
  'virtual_field_name',
);
```

> [!WARNING]
> Be aware to properly quote values, identifiers and sub-expressions.
> No automatic quoting will be applied.

### `ExpressionBuilder::space()`

Create statement containing `$numberOfSpaces` spaces.

> [!NOTE]
> The `SPACE(number)` expression is used for MariaDB and MySQL and
> `ExpressionBuilder::repeat()` expression as fallback for PostgreSQL
> and SQLite.

**Method signature**

```php
/**
 * @param int|string    $numberOfSpaces Expression or value defining how
 *                                      many spaces should be created.
 * @param string        $asIdentifier   Provide result as identifier field
 *                                      (AS), not added if empty string.
 * @return string   Returns the platform compatible statement to create the
 *                  x-times repeated space(s).
 */
public function space(
    string $numberOfSpaces,
    string $asIdentifier = '',
): string {}
```

**Usage example**

```php
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$expression1 = $queryBuilder->expr()->space(
    '10'
);

$expression2 = $queryBuilder->expr()->space(
    '20',
    $queryBuilder->quoteIdentifier('aliased_field'),
);

$expression3 = $queryBuilder->expr()->space(
    '(210)'
);

$expression3 = $queryBuilder->expr()->space(
    '(210)',
    $queryBuilder->quoteIdentifier('aliased_field'),
);

$expression5 = $queryBuilder->expr()->space(
    $queryBuilder->expr()->castInt(
        $queryBuilder->quoteIdentifier('table_repeat_number_field'),
    ),
);

$expression6 = $queryBuilder->expr()->space(
    $queryBuilder->expr()->castInt(
        $queryBuilder->quoteIdentifier('table_repeat_number_field'),
    ),
    $queryBuilder->quoteIdentifier('aliased_field'),
);
```

> [!WARNING]
> Be aware to properly quote values, identifiers and sub-expressions.
> No automatic quoting will be applied.

### `ExpressionBuilder::left()`

Extract `$length` character of `$value` from the left side.

> [!NOTE]
> Creates a `LEFT(string, number_of_chars)` expression for all supported
> database vendors except SQLite, where `substring(string, integer[, integer])`
> is used to provide a compatible expression.

**Method signature**

```php
/**
 * @param int|string    $length         Integer value or expression
 *                                      providing the length as integer.
 * @param string        $value          Value, identifier or expression
 *                                      defining the value to extract from
 *                                      the left.
 * @param string        $asIdentifier   Provide `AS` identifier if not empty.
 * @return string   Return the expression to extract defined substring
 *                  from the right side.
 */
public function left(
    int|string $length,
    string $value,
    string $asIdentifier = '',
): string {}
```

**Usage example**

```php
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$expression1 = $queryBuilder->expr()->left(
    6,
    $queryBuilder->quote('some-string'),
);

$expression2 = $queryBuilder->expr()->left(
    '6',
    $queryBuilder->quote('some-string'),
);

$expression3 = $queryBuilder->expr()->left(
    $queryBuilder->castInt('(23)'),
    $queryBuilder->quote('some-string'),
);

$expression4 = $queryBuilder->expr()->left(
    $queryBuilder->castInt('(23)'),
    $queryBuilder->quoteIdentifier('table_field_name'),
);
```

> [!TIP]
> For other sub string operations, `\Doctrine\DBAL\Platforms\AbstractPlatform::getSubstringExpression()`
> can be used. Synopsis: `getSubstringExpression(string $string, string $start, ?string $length = null): string`.

### `ExpressionBuilder::right()`

Extract `$length` character of `$value` from the right side.

> [!NOTE]
> Creates a `RIGHT(string, number_of_chars)` expression for all supported
> database vendors except SQLite, where `substring(string, integer[, integer])`
> is used to provide a compatible expression.

**Method signature**

```php
/**
 * @param int|string    $length         Integer value or expression
 *                                      providing the length as integer.
 * @param string        $value          Value, identifier or expression
 *                                      defining the value to extract from
 *                                      the right.
 * @param string        $asIdentifier   Provide `AS` identifier if not empty.
 *
 * @return string   Return the expression to extract defined substring
 *                  from the right side.
 */
public function right(
    int|string $length,
    string $value,
    string $asIdentifier = '',
): string {}
```

**Usage example**

```php
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$expression1 = $queryBuilder->expr()->right(
    6,
    $queryBuilder->quote('some-string'),
);

$expression2 = $queryBuilder->expr()->right(
    '6',
    $queryBuilder->quote('some-string'),
);

$expression3 = $queryBuilder->expr()->right(
    $queryBuilder->castInt('(23)'),
    $queryBuilder->quote('some-string'),
);

$expression4 = $queryBuilder->expr()->right(
    $queryBuilder->castInt('(23)'),
    $queryBuilder->quoteIdentifier('table_field_name'),
);
```

> [!WARNING]
> Be aware to properly quote values, identifiers and sub-expressions.
> No automatic quoting will be applied.

### `ExpressionBuilder::leftPad()`

Left-pad the value or sub-expression result with $paddingValue, to a total
length of $length.

> [!NOTE]
> SQLite does not support `LPAD(string, integer, string)`, therefore a
> more complex compatible replacement expression construct is created.

**Method signature**

```php
/**
 * @param string        $value          Value, identifier or expression
 *                                      defining the value which should
 *                                      be left padded.
 * @param int|string    $length         Value, identifier or expression
 *                                      defining the padding length to
 *                                      fill up on the left or crop.
 * @param string        $paddingValue   Padding character used to fill
 *                                      up if characters are missing on
 *                                      the left side.
 * @param string        $asIdentifier   Used to add a field identifier alias
 *                                      (`AS`) if non-empty string (optional).
 * @return string   Returns database connection platform compatible
 *                  left-pad expression.
 */
public function leftPad(
    string $value,
    int|string $length,
    string $paddingValue,
    string $asIdentifier = '',
): string {}
```

**Usage example**

```php
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$expression1 = $queryBuilder->expr()->leftPad(
    $queryBuilder->quote('123'),
    10,
    '0',
);

$expression2 = $queryBuilder->expr()->leftPad(
    $queryBuilder->expr()->castVarchar($queryBuilder->quoteIdentifier('uid')),
    10,
    '0',
);

$expression3 = $queryBuilder->expr()->leftPad(
    $queryBuilder->expr()->concat(
        $queryBuilder->quote('1'),
        $queryBuilder->quote('2'),
        $queryBuilder->quote('3'),
    ),
    10,
    '0',
);

$expression4 = $queryBuilder->expr()->leftPad(
    $queryBuilder->castVarchar('( 1123 )'),
    10,
    '0',
);

$expression5 = $queryBuilder->expr()->leftPad(
    $queryBuilder->castVarchar('( 1123 )'),
    10,
    '0',
    'virtual_field',
);
```

> [!WARNING]
> Be aware to properly quote values, identifiers and sub-expressions.
> No automatic quoting will be applied.

### `ExpressionBuilder::rightPad()`

Right-pad the value or sub-expression result with `$paddingValue`, to a
total length of `$length`.

> [!NOTE]
> SQLite does not support `RPAD(string, integer, string)`, therefore a
> complexer compatible replacement expression construct is created.

**Method signature**

```php
/**
 * @param string        $value          Value, identifier or expression
 *                                      defining the value which should be
 *                                      right padded.
 * @param int|string    $length         Value, identifier or expression
 *                                      defining the padding length to
 *                                      fill up on the right or crop.
 * @param string        $paddingValue   Padding character used to fill up
 *                                      if characters are missing on the
 *                                      right side.
 * @param string        $asIdentifier   Used to add a field identifier alias
 *                                      (`AS`) if non-empty string (optional).
 * @return string   Returns database connection platform compatible
 *                  right-pad expression.
 */
public function rightPad(
    string $value,
    int|string $length,
    string $paddingValue,
    string $asIdentifier = '',
): string {}
```

**Usage example**

```php
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$expression1 = $queryBuilder->expr()->rightPad(
    $queryBuilder->quote('123'),
    10,
    '0',
);

$expression2 = $queryBuilder->expr()->rightPad(
    $queryBuilder->expr()->castVarchar($queryBuilder->quoteIdentifier('uid')),
    10,
    '0',
 );

$expression3 = $queryBuilder->expr()->rightPad(
    $queryBuilder->expr()->concat(
        $queryBuilder->quote('1'),
        $queryBuilder->quote('2'),
        $queryBuilder->quote('3'),
    ),
    10,
    '0',
);

$expression4 = $queryBuilder->expr()->rightPad(
    $queryBuilder->castVarchar('( 1123 )'),
    10,
    '0',
);

$expression5 = $queryBuilder->expr()->rightPad(
    $queryBuilder->quote('123'),
    10,
    '0',
    'virtual_field',
);
```

> [!WARNING]
> Be aware to properly quote values, identifiers and sub-expressions.
> No automatic quoting will be applied.

## Impact

Extension authors can use the new expression methods to build more advanced
queries without the requirement to deal with the correct implementation for
all supported database vendors.