MySQL SPACE() Function

The SPACE() function in MySQL returns a string consisting of a specified number of space characters.

Syntax

SPACE(number)

Parameters

Definition and Usage

• The SPACE() function generates a string with the given number of space characters (' ').
• This function can be used when you need to create a string of spaces for formatting purposes or for data manipulation.

Usage Examples

Example 1: Return a string with 10 space characters

Generate a string that consists of 10 spaces:

SELECT SPACE(10);

Output:

Explanation: The function generates a string with 10 space characters.

Example 2: Use SPACE() to create a formatted output

You can use SPACE() in combination with other string functions to format your output. For example, to pad a string with spaces to align it:

SELECT CONCAT('Hello', SPACE(5), 'World');

Output:

Explanation: The SPACE(5) function inserts 5 spaces between "Hello" and "World", ensuring they are separated by 5 space characters.

Technical Details

• Works In: From MySQL 4.0 onwards.
• Return Type: Returns a string consisting solely of space characters (' '), with the length of the string determined by the number argument.
• Behavior:
  • The number parameter must be a non-negative integer.
  • If the number is 0 or negative, the function returns an empty string.

Applications

1. Formatting Output: Use SPACE() when formatting results, aligning or separating columns of data with a specific number of spaces.
2. Data Padding: It can be used to pad strings or columns in the result set to maintain consistent formatting, especially when displaying tabular data.
3. Generating Placeholders: In some cases, you may want to generate placeholder spaces for use in report generation or data processing.

Key Notes

• The SPACE() function only generates space characters (' '), not other whitespace characters like tabs or newlines.
• If you need to create other kinds of padding, consider combining SPACE() with other string functions like LPAD() or RPAD().