The Easiest Way to Add Multiple Spaces to a String in MySQL – SPACE()

Occasionally when working with MySQL databases, you might find yourself needing to add multiple space characters to a string.

Maybe you need to pad a string by adding leading or trailing spaces. Or maybe you need to replace a single space within the string with many spaces. Either way, MySQL has the SPACE() function to help you.

The SPACE() function is an easy way for you to return as many space characters as you need.

Syntax

Here’s how the syntax goes:

SPACE(N)

Where N is the number of spaces you need returned. So if you need 10 spaces you’d use SPACE(10).

Example

Here’s a quick example to demonstrate how SPACE() works:

SELECT CONCAT('Cherry', SPACE(8), 'Blossom') AS Result;

Result:

+-----------------------+
| Result                |
+-----------------------+
| Cherry        Blossom |
+-----------------------+

In this case, I concatenate two strings and add 8 spaces between them (so strictly speaking, I’m actually concatenating three strings). To do this, I use the SPACE() function as one of the arguments to the CONCAT() function.

This has the same result as actually typing out all 8 spaces:

SELECT CONCAT('Cherry', '        ', 'Blossom') AS Result;

Result:

+-----------------------+
| Result                |
+-----------------------+
| Cherry        Blossom |
+-----------------------+

The difference is that you don’t have to type out all those characters. Plus using SPACE()makes the code easier to read.

Another Option: The REPEAT() Function

Another option is to use the REPEAT() function. This works similar to SPACE() except that REPEAT() allows you to specify other characters (i.e. not just spaces).

SELECT CONCAT('Cherry', REPEAT(' ', 8), 'Blossom') AS Result;

Result:

+-----------------------+
| Result                |
+-----------------------+
| Cherry        Blossom |
+-----------------------+

Although, as you can see, this requires some extra code that you don’t need to specify when using the SPACE() function. Having said that, there may be times where REPEAT()works better for a particular scenario.