[sql] How to deal with SQL column names that look like SQL keywords?

One of my columns is called from. I can't change the name because I didn't make it. Am I allowed to do something like SELECT from FROM TableName or is there a special syntax to avoid the SQL Server being confused?

In Apache Drill, use backquotes:

select `from` from table;

The following will work perfectly:

SELECT DISTINCT table.from AS a FROM table

Your question seems to be well answered here, but I just want to add one more comment to this subject.

Those designing the database should be well aware of the reserved keywords and avoid using them. If you discover someone using it, inform them about it (in a polite way). The keyword here is reserved word.

More information:

"Reserved keywords should not be used as object names. Databases upgraded from earlier versions of SQL Server may contain identifiers that include words not reserved in the earlier version, but that are reserved words for the current version of SQL Server. You can refer to the object by using delimited identifiers until the name can be changed." http://msdn.microsoft.com/en-us/library/ms176027.aspx

and

"If your database does contain names that match reserved keywords, you must use delimited identifiers when you refer to those objects. For more information, see Identifiers (DMX)." http://msdn.microsoft.com/en-us/library/ms132178.aspx

While you are doing it - alias it as something else (or better yet, use a view or an SP and deprecate the old direct access method).

SELECT [from] AS TransferFrom -- Or something else more suitable
FROM TableName

In MySQL, alternatively to using back quotes (`), you can use the UI to alter column names. Right click the table > Alter table > Edit the column name that contains sql keyword > Commit.

select [from] from <table>

As a note, the above does not work in MySQL

Your question seems to be well answered here, but I just want to add one more comment to this subject.

Those designing the database should be well aware of the reserved keywords and avoid using them. If you discover someone using it, inform them about it (in a polite way). The keyword here is reserved word.

More information:

"Reserved keywords should not be used as object names. Databases upgraded from earlier versions of SQL Server may contain identifiers that include words not reserved in the earlier version, but that are reserved words for the current version of SQL Server. You can refer to the object by using delimited identifiers until the name can be changed." http://msdn.microsoft.com/en-us/library/ms176027.aspx

and

"If your database does contain names that match reserved keywords, you must use delimited identifiers when you refer to those objects. For more information, see Identifiers (DMX)." http://msdn.microsoft.com/en-us/library/ms132178.aspx

Hi I work on Teradata systems that is completely ANSI compliant. Use double quotes " " to name such columns.

E.g. type is a SQL reserved keyword, and when used within quotes, type is treated as a user specified name.

See below code example:

CREATE TABLE alpha1
AS
(
SEL
product1
type_of_product AS "type"
FROM beta1
) WITH DATA
PRIMARY INDEX (product1)

--type is a SQL reserved keyword

TYPE

--see? now to retrieve the column you would use:

SEL "type" FROM alpha1

I have also faced this issue. And the solution for this is to put [Column_Name] like this in the query.

string query= "Select [Name],[Email] from Person";

So it will work perfectly well.

If it had been in PostgreSQL, use double quotes around the name, like:

select "from" from "table";

Note: Internally PostgreSQL automatically converts all unquoted commands and parameters to lower case. That have the effect that commands and identifiers aren't case sensitive. sEleCt * from tAblE; is interpreted as select * from table;. However, parameters inside double quotes are used as is, and therefore ARE case sensitive: select * from "table"; and select * from "Table"; gets the result from two different tables.

These are the two ways to do it:

1. Use back quote as here:

SELECT `from` FROM TableName

2. You can mention with table name as:

SELECT TableName.from FROM TableName

If it had been in PostgreSQL, use double quotes around the name, like:

select "from" from "table";

Note: Internally PostgreSQL automatically converts all unquoted commands and parameters to lower case. That have the effect that commands and identifiers aren't case sensitive. sEleCt * from tAblE; is interpreted as select * from table;. However, parameters inside double quotes are used as is, and therefore ARE case sensitive: select * from "table"; and select * from "Table"; gets the result from two different tables.

Your question seems to be well answered here, but I just want to add one more comment to this subject.

Those designing the database should be well aware of the reserved keywords and avoid using them. If you discover someone using it, inform them about it (in a polite way). The keyword here is reserved word.

More information:

"Reserved keywords should not be used as object names. Databases upgraded from earlier versions of SQL Server may contain identifiers that include words not reserved in the earlier version, but that are reserved words for the current version of SQL Server. You can refer to the object by using delimited identifiers until the name can be changed." http://msdn.microsoft.com/en-us/library/ms176027.aspx

and

"If your database does contain names that match reserved keywords, you must use delimited identifiers when you refer to those objects. For more information, see Identifiers (DMX)." http://msdn.microsoft.com/en-us/library/ms132178.aspx

The following will work perfectly:

SELECT DISTINCT table.from AS a FROM table

In Apache Drill, use backquotes:

select `from` from table;

If you ARE using SQL Server, you can just simply wrap the square brackets around the column or table name.

select [select]
from [table]

While you are doing it - alias it as something else (or better yet, use a view or an SP and deprecate the old direct access method).

SELECT [from] AS TransferFrom -- Or something else more suitable
FROM TableName

If it had been in PostgreSQL, use double quotes around the name, like:

select "from" from "table";

Note: Internally PostgreSQL automatically converts all unquoted commands and parameters to lower case. That have the effect that commands and identifiers aren't case sensitive. sEleCt * from tAblE; is interpreted as select * from table;. However, parameters inside double quotes are used as is, and therefore ARE case sensitive: select * from "table"; and select * from "Table"; gets the result from two different tables.

I have also faced this issue. And the solution for this is to put [Column_Name] like this in the query.

string query= "Select [Name],[Email] from Person";

So it will work perfectly well.

While you are doing it - alias it as something else (or better yet, use a view or an SP and deprecate the old direct access method).

SELECT [from] AS TransferFrom -- Or something else more suitable
FROM TableName

Judging from the answers here and my own experience. The only acceptable answer, if you're planning on being portable is don't use SQL keywords for table, column, or other names.

All these answers work in the various databases but apparently a lot don't support the ANSI solution.

If you ARE using SQL Server, you can just simply wrap the square brackets around the column or table name.

select [select]
from [table]

I ran in the same issue when trying to update a column which name was a keyword. The solution above didn't help me. I solved it out by simply specifying the name of the table like this:

UPDATE `survey`
SET survey.values='yes,no'
WHERE (question='Did you agree?')

These are the two ways to do it:

1. Use back quote as here:

SELECT `from` FROM TableName

2. You can mention with table name as:

SELECT TableName.from FROM TableName

Your question seems to be well answered here, but I just want to add one more comment to this subject.

Those designing the database should be well aware of the reserved keywords and avoid using them. If you discover someone using it, inform them about it (in a polite way). The keyword here is reserved word.

More information:

"Reserved keywords should not be used as object names. Databases upgraded from earlier versions of SQL Server may contain identifiers that include words not reserved in the earlier version, but that are reserved words for the current version of SQL Server. You can refer to the object by using delimited identifiers until the name can be changed." http://msdn.microsoft.com/en-us/library/ms176027.aspx

and

"If your database does contain names that match reserved keywords, you must use delimited identifiers when you refer to those objects. For more information, see Identifiers (DMX)." http://msdn.microsoft.com/en-us/library/ms132178.aspx

In MySQL, alternatively to using back quotes (`), you can use the UI to alter column names. Right click the table > Alter table > Edit the column name that contains sql keyword > Commit.

select [from] from <table>

As a note, the above does not work in MySQL

You can put your column name in bracket like:

Select  [from] from < ur_tablename>

Or

Put in a temprary table then use as you like.
Example:

Declare @temp_table table(temp_from varchar(max))

Insert into @temp_table
Select * from your_tablename

Here I just assume that your_tablename contains only one column (i.e. from).

If you ARE using SQL Server, you can just simply wrap the square brackets around the column or table name.

select [select]
from [table]

You can put your column name in bracket like:

Select  [from] from < ur_tablename>

Or

Put in a temprary table then use as you like.
Example:

Declare @temp_table table(temp_from varchar(max))

Insert into @temp_table
Select * from your_tablename

Here I just assume that your_tablename contains only one column (i.e. from).

If it had been in PostgreSQL, use double quotes around the name, like:

select "from" from "table";

Note: Internally PostgreSQL automatically converts all unquoted commands and parameters to lower case. That have the effect that commands and identifiers aren't case sensitive. sEleCt * from tAblE; is interpreted as select * from table;. However, parameters inside double quotes are used as is, and therefore ARE case sensitive: select * from "table"; and select * from "Table"; gets the result from two different tables.

Hi I work on Teradata systems that is completely ANSI compliant. Use double quotes " " to name such columns.

E.g. type is a SQL reserved keyword, and when used within quotes, type is treated as a user specified name.

See below code example:

CREATE TABLE alpha1
AS
(
SEL
product1
type_of_product AS "type"
FROM beta1
) WITH DATA
PRIMARY INDEX (product1)

--type is a SQL reserved keyword

TYPE

--see? now to retrieve the column you would use:

SEL "type" FROM alpha1

If you ARE using SQL Server, you can just simply wrap the square brackets around the column or table name.

select [select]
from [table]

While you are doing it - alias it as something else (or better yet, use a view or an SP and deprecate the old direct access method).

SELECT [from] AS TransferFrom -- Or something else more suitable
FROM TableName

Judging from the answers here and my own experience. The only acceptable answer, if you're planning on being portable is don't use SQL keywords for table, column, or other names.

All these answers work in the various databases but apparently a lot don't support the ANSI solution.