Use of reserve words in a query using SQL Server

I am using SQL Server 2017 Express Edition.  I have a table (T1) that has a column named case.  There are duplicate values in the column case. Hence I ran a query,

Select * from T1 order by id;    --- This runs fine.
But
Select * from T1 order by Case;   -- Gives error-- Msg 102, Level 15, State 1, Line 23  Incorrect syntax near ';'
Kamal AgnihotriAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Case is keyword in SQL Server that why it is not working.

Please try this  , use square brackets around that.

Select * from T1 order by [Case]

Also avoid using Keywords as column names and identifiers.
0
Pawan KumarDatabase ExpertCommented:
Sample trail for you. You need check the last case 3. ( The last case )

Data insertion and Table creation

CREATE TABLE cars
(
	[case] INT
	,ID INT
)
GO

INSERT INTO cars 
VALUES ( 1,1) , ( 2,5 ) , (10,-1 ) 
GO

Open in new window


TRIAL 1 - order by ID

OUTPUT

/*------------------------
SELECT * FROM cars
ORDER By ID 
------------------------*/
case        ID
----------- -----------
10          -1
1           1
2           5

(3 row(s) affected)

Open in new window


TRIAL 2 - order by case WITH out square brackets


/*------------------------
SELECT * FROM cars
ORDER By case
------------------------*/
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'case'.

Open in new window


TRIAL 3 - order by [case] /*WITH SQUARE BRACKETS*/

OUTPUT

/*------------------------
SELECT * FROM cars
ORDER By [case]
------------------------*/
case        ID
----------- -----------
1           1
2           5
10          -1

(3 row(s) affected)

Open in new window

0
Pawan KumarDatabase ExpertCommented:
You can also use SingleQuotes...or double quotes(Tested these in 2016)

SELECT * FROM cars
ORDER By 'case'

OUTPUT

/*------------------------ SELECT * FROM cars ORDER By 'case' ------------------------*/ case ID ----------- ----------- 1 1 2 5 10 -1 (3 row(s) affected)


SELECT * FROM cars
ORDER By "case"

OUTPUT

/*------------------------

SELECT * FROM cars
ORDER By "case"
------------------------*/
case        ID
----------- -----------
1           1
2           5
10          -1

(3 row(s) affected)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Or better, rename your column to a non-keyword, like caseinfo or casedata or caseflag.

If you leave the field named to an SQL reserved word, you'll continually have to deal with additional SQL syntax to escape this field, so you can reference it's data.

This can turn into a huge mess of additional SQL machinations (cumbersome syntax), which makes SQL harder to read + even harder to manage.

In fact, many backup + restore tools will simply die if you do this, so unless you're very careful making your backups, you may end up with either broken/incomplete backups or backups which require some special tool or additional cumbersome syntax to correctly restore.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Pawan, 'case' is a varchar, and not "valid" for this purpose. Sorting for a varchar literal doesn't do anything ;-).
0
Kamal AgnihotriAuthor Commented:
Thanks every one. The solution offered worked. Question can be closed.
0
Kamal AgnihotriAuthor Commented:
Thanks Pawan Kumar. I find SQL Server more complicated than Oracle.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.