How SPACE() Works in MariaDB

In MariaDB, SPACE() is a built-in string function that returns a given number of space characters.

You provide the number of spaces to return when you call the function.

Syntax

The syntax goes like this:

SPACE(N)

Where N is the number of spaces to return.

Example

Here’s a basic example:

SELECT 
    SPACE(5) AS "5",
    SPACE(10) AS "10",
    SPACE(20) AS "20";

Result:

+-------+------------+----------------------+
| 5     | 10         | 20                   |
+-------+------------+----------------------+
|       |            |                      |
+-------+------------+----------------------+

In this example I called SPACE() multiple times, each with a different value. I did this so that you can see the effect of the SPACE() function with each value. We can see that the width of the columns increase as more spaces are applied.

Here’s another example where I use the CONCAT() function to concatenate two words, while using SPACE() to add space between them:

SELECT CONCAT('Stay', SPACE(10), 'Away!');

Result:

+------------------------------------+
| CONCAT('Stay', SPACE(10), 'Away!') |
+------------------------------------+
| Stay          Away!                |
+------------------------------------+

Zero Spaces

Passing an argument of 0 (zero) results in no space:

SELECT CONCAT('Come', SPACE(0), 'Here!');

Result:

+-----------------------------------+
| CONCAT('Come', SPACE(0), 'Here!') |
+-----------------------------------+
| ComeHere!                         |
+-----------------------------------+

Negative Spaces

Passing a negative argument results in no space:

SELECT CONCAT('Come', SPACE(-5), 'Here!');

Result:

+------------------------------------+
| CONCAT('Come', SPACE(-5), 'Here!') |
+------------------------------------+
| ComeHere!                          |
+------------------------------------+

Null Arguments

Passing null returns null:

SELECT SPACE(null);

Result:

+-------------+
| SPACE(null) |
+-------------+
| NULL        |
+-------------+

This will also mess up any concatenation operation you might be doing:

SELECT CONCAT('Come', SPACE(null), 'Here!');

Result:

+--------------------------------------+
| CONCAT('Come', SPACE(null), 'Here!') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+

Missing Argument

Calling SPACE() with the wrong number of arguments, or without passing any arguments results in an error:

SELECT SPACE();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'SPACE'