SQL - SPACE() Function

Quiz

The SQL SPACE() function is used to retrieve the string containing the specified number of spaces.

It accepts a numeric( say N) value as a parameter and returns a string consisting of N space characters. If the parameter has a negative value, this method returns a NULL string. The function return type is VARCHAR.

Note − To include spaces in Unicode data, or to return more than 8000 character spaces, use the REPLICATE() function instead of the SPACE() function.

Syntax

Following is the syntax of the SQL SPACE() function −

SPACE(int_exp)

Parameters

int_exp − It is a positive numeric value that indicates the number of spaces.

Return value

This function returns a string consisting of N number of space characters.

Example

In the following example,we are using the SQL SPACE() function to retrieve a new string contains the specified number 5 of spaces.

SELECT SPACE(5);

Output

Following is the output of the above query −

+----------+
| SPACE(5) |
+----------+
|          |
+----------+

Example

Following is another example of the SQL SPACE() function, we are passing 30 as an argument to function to get the string with the specified number of spaces.

SELECT SPACE(30);

Output

On executing the above program, it will produce the following output −

+--------------------------------+
| SPACE(30)                      |
+--------------------------------+
|                                |
+--------------------------------+

Example

In the following SQL query we are using the CONCATE() function along with the SPACE() function to concatenate the spaces and strings together.

SELECT CONCAT('Hello', SPACE(10), 'TutorialsPoint');

Output

On executing the above program, it will produces the following output −

+----------------------------------------------+
| CONCAT('Hello', SPACE(10), 'TutorialsPoint') |
+----------------------------------------------+
| Hello          TutorialsPoint                |
+----------------------------------------------+

Example

You can pass the table column as an argument to the SPACE() function to retrieve the content of a column containing specified spaces. Assume we have created a table with the name Customers using the CREATE statement as follows −

CREATE TABLE CUSTOMERS(    
ID INT NOT NULL,    
FIRST_NAME VARCHAR (20),
LAST_NAME VARCHAR(20),
AGE INT NOT NULL,    
ADDRESS CHAR (25) ,    
SALARY DECIMAL (18, 2));

Now let's insert 4 records into the Customers table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES (1, 'Ramesh','KUMAR', 32, 'Ahmedabad', 2000.00 ); 
INSERT INTO CUSTOMERS VALUES (2, 'Khilan','Verma', 25, 'Delhi', 1500.00 ); 
INSERT INTO CUSTOMERS VALUES (3, 'kaushik','Gupta', 23, 'Kota', 2000.00 ); 
INSERT INTO CUSTOMERS VALUES (4, 'Chaitali','Pal', 25, 'Mumbai', 6500.00 );

Following SQL query concatenate the FIRST_NAME and LAST_NAME columns and displays it as NAME column in a Customers table, and using the SPACE() function we will separate them with 3 spaces −

SELECT ID, FIRST_NAME, CONCAT(FIRST_NAME, SPACE(3), LAST_NAME) AS NAME FROM CUSTOMERS;

Output

The above SQL query produces the following output −

+----+------------+-----------------+
| ID | FIRST_NAME | NAME            |
+----+------------+-----------------+
|  1 | Ramesh     | Ramesh   KUMAR  |
|  2 | Khilan     | Khilan   Verma  |
|  3 | kaushik    | kaushik   Gupta |
|  4 | Chaitali   | Chaitali   Pal  |
+----+------------+-----------------+

Example

If the parameter value is negative, this method returns an empty or NULL string.

In the following example,we are using the SPACE() function to retrieve a string containing the -3 number of spaces.

SELECT SPACE(-3);

Output

Following is the output of the above query −

+-----------+
| SPACE(-3) |
+-----------+
|     NULL  |
+-----------+

sql-string-functions.htm

Print Page