'Select a column with a keyword name

I have a table named 'test'

It contains a column named 'AND'

For that I use the following queries

insert into test values ('a','abc');
insert into test values ('b','def');

Now I want to select my 'AND' column from the 'test' table

The query select AND from test; wont work because AND is keyword

I tried 

select "AND" from test;
select 'AND' from test;

Both queries return the wrong results as show below

enter image description here

How can I select my 'AND' column?

mysqlsqlkeywordcolumnname


Solution 1:[1]

use backtick to escape keyword instead. just like how you created your table. using double quote or single quote will parse it into string that is why you're getting two records of and

select `AND` from test;

Solution 2:[2]

  1. Don't create columns with a name that is a keyword.

  2. If you do create a column with such a name, use backticks (MySQL only) or double quotes (SQL standard) to enclose the name. I'm not certain whether you need to switch on some mechanism to have double quotes work in MySQL. This creates a 'delimited identifier'.

    SELECT `AND` AS a, "AND" AS b
  FROM test;

See also:

Solution 3:[3]

Its a SO old question and solved. Read here more.

But still you can Try this :

select your_tablename.column_name from table_name;

Your code :-

select test.AND from test;

Solution 4:[4]

If you want to use reserved word as column, you must use backtick around the reserved word column.....

In your case, you have to do like this:

select `AND' from test;

Solution 5:[5]

From your question:

CREATE TABLE `test` (
    `count` VARCHAR(50) NULL DEFAULT NULL,
    `AND` VARCHAR(50) NULL DEFAULT NULL
----^---^ Why do you use `backtick` (`) in CREATE TABLE statement?
)

Same way you need to use backtick in SELECT statement.

SELECT `AND` FROM test;
-------^---^-----------

See MySQL: Reserved Words

Solution 6:[6]

If you use select setement using "" it select a given sting so you have to give a column name like in mssql you have to use

select [AND] from test;

or in mysql syntex select AND from test;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Community
Solution 3 Community
Solution 4 Akash KC
Solution 5
Solution 6

Related Questions