How the SPACE() function works in Mariadb?

The SPACE() function in MariaDB is a simple string function that returns a string consisting of a specified number of space characters.

Syntax

The syntax for the MariaDB SPACE() function is as follows:

SPACE(number)

The SPACE() function takes a single argument, number, which is an integer representing the number of space characters you want to generate. The function returns a string of spaces with the length equal to the number provided.

Examples

Example 1: Generating Spaces

This example demonstrates how to generate a string of 5 space characters.

SELECT SPACE(5);

The output for this statement is:

+----------+
| SPACE(5) |
+----------+
|          |
+----------+

This indicates that the SPACE() function has generated 5 space characters.

Example 2: Padding a String with Spaces

In this example, we will pad the left side of a string with spaces.

SELECT CONCAT(SPACE(3), 'text');

The output for this statement is:

+--------------------------+
| CONCAT(SPACE(3), 'text') |
+--------------------------+
|    text                  |
+--------------------------+

This shows that ’text’ has been padded with 3 spaces on the left.

Example 3: Using SPACE() with Other Functions

This example shows how SPACE() can be used with the REPEAT() function to create a pattern.

SELECT REPEAT(CONCAT('word', SPACE(1)), 3);

The output for this statement is:

+-------------------------------------+
| REPEAT(CONCAT('word', SPACE(1)), 3) |
+-------------------------------------+
| word word word                      |
+-------------------------------------+

This demonstrates the use of SPACE() to separate repeated words.

Example 4: SPACE() in a Table

Here, we use the SPACE() function to format the output from a table.

DROP TABLE IF EXISTS items;
CREATE TABLE items (id INT, name VARCHAR(100));
INSERT INTO items VALUES (1, 'Apple'), (2, 'Banana');

SELECT CONCAT(name, SPACE(10 - LENGTH(name)), 'is a fruit.') AS formatted_text FROM items;

The output for this statement is:

+-----------------------+
| formatted_text        |
+-----------------------+
| Apple     is a fruit. |
| Banana    is a fruit. |
+-----------------------+

This illustrates using SPACE() to align text in a column.

Example 5: Handling Negative Numbers

This example highlights how the SPACE() function handles negative numbers.

SELECT SPACE(-5);

The output for this statement is:

+-----------+
| SPACE(-5) |
+-----------+
|           |
+-----------+

The SPACE() function returns an empty string when given a negative number.

Related Functions

Below are a few functions related to the MariaDB SPACE() function:

• MariaDB CONCAT() function is used to concatenate strings, often used with SPACE() to add spacing between strings.
• MariaDB REPEAT() function repeats a string a specified number of times, which can be combined with SPACE() to create patterns or formatting.

Conclusion

The SPACE() function is a useful tool in MariaDB for creating space-filled strings, which can be especially handy for formatting text outputs. It’s a straightforward function that, when combined with other string functions, can help achieve various text manipulation tasks efficiently. Remember that passing a negative number to SPACE() will result in an empty string, so it’s important to handle such cases appropriately in your queries.