Nisarg Upadhyay
SELECT Query with space in columns name without errors

How to write SQL queries with spaces in column names

September 29, 2021 by

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’.

SQL Server Table name with space

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

SQL Server Table name with space in square bracket

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

SQL Server Table name with space in 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.

SQL Server column name with space

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

SQL Server column name with space in square bracket

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

SQL Server column name with space in 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’.

SELECT Query with space in columns name

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

SELECT Query with space in columns name without errors

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.

INSERT SQL Query with space in columns name

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

UPDATE SQL Query with space in columns name

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

DELETE  SQL Query with space in columns name

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

Create table with space in name

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

Create table with space in name

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),

Create table with space in column name

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

Create table with space in column name with backtics

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

SELECT  MySQL Query with space in columns name

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

SELECT  SQL Query with space in columns name with backticks

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.

INSERT  MySQL Query with space in columns name

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

UPDATE  MySQL Query with space in columns name

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

DELETE MySQL Query with space in columns name

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

Nisarg Upadhyay
Development, MySQL, SQL commands, T-SQL

About 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

1,212 Views