MySQL SPACE() function

SPACE() function

MySQL SPACE() returns the string containing a number of spaces specified as an argument.

This function is useful in -

• Padding: A string of spaces can be used to pad or align text or numbers.
• Formatting: SPACE() can be used to create spaces for formatting purposes, such as indenting or aligning columns.
• Placeholder generation: The SPACE() function can be used to generate a string of spaces as a placeholder for future data.

Syntax:

SPACE(N)

Argument:

MySQL Version: 8.0

Example -1: MySQL SPACE() function

Code:

SELECT 'start', SPACE(10), 'end'; 

Explanation:

The above MySQL statement returns a string containing 10 number of spaces displayed in column alias SPACE(10) in the output.

Output:

mysql> SELECT 'start', SPACE(10), 'end';
+-------+------------+-----+
| start | SPACE(10)  | end |
+-------+------------+-----+
| start |            | end | 
+-------+------------+-----+
1 row in set (0.00 sec)

Example -2: MySQL SPACE() function

The above MySQL statement returns a string containing 10 number of spaces as column alias space(10) along with columns ‘aut_id’ and ‘aut_name’ for those rows from the table author which have the column value of country as the ‘USA’.

Code:

SELECT aut_id,aut_name,aut_id,space(10), aut_name 
FROM author 
WHERE country='USA';  

Sample table: author

Output:

mysql> SELECT aut_id,aut_name,aut_id,space(10), aut_name 
    -> FROM author 
    -> WHERE country='USA';
+--------+---------------+--------+------------+---------------+
| aut_id | aut_name      | aut_id | space(10)  | aut_name      |
+--------+---------------+--------+------------+---------------+
| AUT006 | Thomas Merton | AUT006 |            | Thomas Merton | 
| AUT008 | Nikolai Dewey | AUT008 |            | Nikolai Dewey | 
| AUT010 | Joseph Milton | AUT010 |            | Joseph Milton | 
| AUT015 | Butler Andre  | AUT015 |            | Butler Andre  | 
+--------+---------------+--------+------------+---------------+
4 rows in set (0.00 sec)

Video Presentation:

All String Functions (Slides presentation)

Previous: SOUNDS_LIKE
Next: STRCMP