SQL SPACE()

The SQL SPACE() function generates a string of repeated space characters, useful for formatting output, padding, or creating consistent visual spacing between text elements.

The SPACE() function takes an integer as input to determine the number of spaces generated.

In this tutorial, we will go through SQL FORMAT() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL SPACE() Function

The basic syntax of the SQL SPACE() function is:

</>
Copy
SPACE(number_of_spaces);

Each part of this syntax has a specific purpose:

  • number_of_spaces: The number of spaces to generate. This must be a positive integer value.

The SPACE() function returns a string containing the specified number of spaces.

Setup for Examples: Creating the Database and Table

We’ll create a sample employees table to demonstrate the SPACE() function examples with fields like first_name, last_name, city, and state.

1. First, create a new database called company:

</>
Copy
CREATE DATABASE company;

2. Select the company database to work with:

</>
Copy
USE company;

3. Create the employees table with first_name, last_name, city, and state fields:

</>
Copy
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    city VARCHAR(50),
    state VARCHAR(50)
);

4. Insert sample data into the employees table:

</>
Copy
INSERT INTO employees (first_name, last_name, city, state)
VALUES ('John', 'Doe', 'New York', 'NY'),
       ('Jane', 'Smith', 'Los Angeles', 'CA'),
       ('Emily', 'Johnson', 'Chicago', 'IL');