SQL Server String SPACE() Function

SQL Server SPACE() Function

The SQL Server SPACE() function is used to return a string of repeated spaces. This function is useful for formatting output or padding strings in SQL queries.

---

Syntax

SELECT SPACE(number_of_spaces);

The SPACE() function takes a single argument:

• number_of_spaces: The number of spaces to return.

---

Example SQL Server SPACE() Function Queries

Let's look at some examples of SQL Server SPACE() function queries:

1. Basic SPACE() Example

SELECT SPACE(10) AS result;

This query returns a string of 10 spaces. The result will be:

result
-----------
          

2. Concatenating Strings with SPACE()

SELECT 'Hello' + SPACE(5) + 'World' AS result;

This query concatenates the string 'Hello' with 5 spaces and then 'World'. The result will be:

result
----------------
Hello     World

3. SPACE() with a Column

SELECT first_name, last_name, first_name + SPACE(3) + last_name AS full_name
FROM employees;

This query concatenates the first_name and last_name columns with 3 spaces in between for each employee. The result will show the first_name, last_name, and the concatenated full_name.

4. SPACE() with a Variable

DECLARE @numSpaces INT;
SET @numSpaces = 4;
SELECT 'Data' + SPACE(@numSpaces) + 'Science' AS result;

This query uses a variable to specify the number of spaces to be added between 'Data' and 'Science'. The result will be:

result
-------------
Data    Science

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the SPACE() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

In this example, we create a table named example_table with columns for id, first_name, and last_name.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, first_name, last_name) VALUES (1, 'John', 'Doe');
INSERT INTO example_table (id, first_name, last_name) VALUES (2, 'Jane', 'Smith');
INSERT INTO example_table (id, first_name, last_name) VALUES (3, 'Alice', 'Johnson');

Here, we insert data into the example_table.

Step 3: Using the SPACE() Function

This step involves using the SPACE() function to format the full name of each entry by adding spaces between the first and last names.

SELECT id, first_name, last_name, first_name + SPACE(2) + last_name AS full_name
FROM example_table;

This query retrieves the id, first_name, last_name, and the formatted full_name for each row in the example_table. The result will be:

id  first_name  last_name  full_name
--- ----------- ---------- --------------
1   John        Doe        John  Doe
2   Jane        Smith      Jane  Smith
3   Alice       Johnson    Alice  Johnson

---

Conclusion

The SQL Server SPACE() function is a powerful tool for returning a string of repeated spaces. Understanding how to use the SPACE() function and its syntax is essential for effective string manipulation and data formatting in SQL Server.