In this article, we are going to learn how we can write a SQL query with space in the column name. Blanks spaces are restricted in the naming convention of the database object’s name and column name of the table. If you want to include the blanks space in the object name or column name, the query and application code must be written differently. You must be careful and precise while writing dynamic SQL queries. This article explains how we can handle object names and columns with blank space in SQL Server and MySQL.

How to write a SQL query with spaces in column names in SQL Server

In SQL Server, we can specify the column name with space in square bracket or parenthesis. Let us understand the concept with some examples.

Space in the database object name

Suppose we want to create a table named Employee table. We create the following query to create the table:

When we execute the query, we will receive the following error:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘Table’.

To fix this error, we can specify the table name in the Square bracket.

Alternatively, you can specify the table name in between double-quotes (“).

Space in the column’s name

Suppose we want to create a table named tblCountries. The table has two columns named country code and country name. Following is the create table statement.

To fix this error, we must specify the column name in the Square bracket.

Alternatively, you can specify the column name in between double quotes (“).

The SELECT statement with space in the column’s name

You can use the square brackets to populate the columns with space in the name. Suppose we want to get the country code and country name columns from the tblCountries table.

The SELECT statement returns an error:

Msg 207, Level 16, State 1, Line 2
Invalid column name ‘country’.
Msg 207, Level 16, State 1, Line 3
Invalid column name ‘country’.

We must specify the column in the square bracket or double quote. The query should be written as follows.

DML SQL query with space in a column name

When we run INSERT, UPDATE, and DELETE statements, we must use a square bracket or double quotes to handle the column name with space. In the following example, I am inserting some records in tblCountries. The table has country code and country name columns.

Suppose we want to change the country name from India to Bharat using an UPDATE statement.

Suppose we want to delete the country whose code is AUS using the DELETE statement.

Now, let us understand how we can write SQL Queries with space in columns name in MySQL Server 8.0

How to Write SQL query with space in column name in MySQL

Space in the database object name

Suppose we want to create a table named film list. We run the following query to create the table

When we execute the query, we will receive the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘list

To fix this error, we can specify the table name in the backtick (`). The CREATE TABLE statement be written as follows:

Space in the column’s name

Suppose we want to create a table named tblmultiplex. The table has two columns named multiplex name and Total Cinema Screen. Following is the create table statement.

When we execute the query, we will encounter the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘name varchar(500),

To fix this error, we can use the backticks. The create tables statement should be written as follows:

The SELECT statement with space in the column’s name

You can use the backticks to populate the columns with space in the name. Suppose we want to populate the multiplex name and Total Cinema Screen columns from the tblmultiplex table.

We will encounter the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘screen from tblmultiplex’ at line 1

Let us write the SELECT queries using backticks. It should be written as follows:

As you can see, the query was executed successfully.

DML SQL query with spaces in the column names

When we run INSERT, UPDATE, and DELETE statements on MySQL Server, we must use backticks to handle the column name with space. In the following example, I am inserting some records in the tblmultiplex table. The table has the multiplex name and total cinema screen columns.

Suppose we want to change the multiplex name from Big Cinema Inc to Cinemax using the UPDATE statement.

Suppose we want to delete the details of the multiplex whose name is PVR cinema using the DELETE statement.

Summary

In this article, we learned how we could write a SQL Query with spaces in the column names. I have covered the following:

1. Create a SQL Server and MySQL table with:
   1. Blank spaces in the table name
   2. Blank spaces in the column names of the table
2. How to run the DML statements on the columns with space. The DML statements were executed on SQL Server and MySQL Server

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022