SQL SPACE

What is the SQL SPACE function?

Returns a string made up of a specified number of blank space characters.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SPACE: SQL Server, Azure SQL, MySQL, MariaDB, SAP HANA, Teradata, Snowflake. Not supported natively in PostgreSQL or SQLite.

SQL SPACE Full Explanation

SPACE is a scalar string function that generates a varchar string consisting only of space (ASCII 32) characters. It is commonly used to pad text, build fixed-width files, or concatenate readable output inside queries and stored procedures. The function takes a single numeric argument that indicates how many spaces to return. If the argument is zero, an empty string is returned; if it is NULL, the result is NULL. Most engines cap the maximum length at the platform’s varchar limit (for example 8,000 or 65,535 characters). Passing a negative number raises an error in some dialects and is silently converted to zero in others.

SQL SPACE Syntax

SPACE ( <integer_expression> );

SQL SPACE Parameters

Example Queries Using SQL SPACE

-- Pad an employee code to 10 characters in SQL Server
SELECT emp_code + SPACE(10 - LEN(emp_code)) AS padded_code
FROM   hr.employees;

-- Create a fixed-width export line in MySQL
SELECT CONCAT(order_id, SPACE(5), customer_id, SPACE(3), total_amount)
FROM   sales.orders
LIMIT  2;

-- Return a blank line for formatting
SELECT SPACE(50) AS blank_line;

Expected Output Using SQL SPACE

Each query returns a varchar column containing the requested count of space characters
In the padding example, shorter employee codes are right-padded so all rows reach a length of 10

Use Cases with SQL SPACE

Align fixed-width report columns
Insert deliberate indentation or blank lines in generated text
Right-pad identifiers before concatenation
Quickly produce a string of spaces without REPEAT or manual typing

Common Mistakes with SQL SPACE

Using negative or decimal arguments – only non-negative integers are valid
Expecting SPACE to exist in PostgreSQL or SQLite (use REPEAT(' ', n) instead)
Forgetting that SPACE(NULL) returns NULL, not an empty string
Generating a string longer than the database’s varchar limit

First Introduced In

SQL Server 7.0 and MySQL 3.23

Frequently Asked Questions

What does SQL SPACE(n) return?

It returns a string containing exactly n blank space characters (ASCII 32). If n is 0, an empty string is returned.

Can I use SPACE to left-pad numbers?

Yes. Combine SPACE with string concatenation or with RIGHT/LEFT to pad values where lpad/rpad are unavailable.

Does SPACE work with Unicode data types?

In SQL Server, SPACE returns varchar. Explicitly CAST to NVARCHAR if you need Unicode.

How do I replace SPACE in PostgreSQL?

Use REPEAT(' ', n) which produces the same string of spaces and is ANSI-compliant.