Solved

SELECT clause identifiers...

Posted on 2015-02-08
10
98 Views
Last Modified: 2015-02-08
Regular identifiers:
- OrderAmount


Irregular identifiers:   <-- Q1: Is use of the word irregular correct here?
- Order Amount         <-- has space
- 2ndAddress            <-- starts with a number
- !TheReason            <-- starts with sign other than (_, @, or #)
- TheReason!            <-- it contains sign other than (_, @, or #)
- From                       <-- it is a T-SQL reserved word.

Q2: Are the SELECT clauses bellow correct?

SELECT "Order Amount", "2ndAddress", "!TheReason", "TheReason!", "From" FROM SomeTable

or

SELECT [Order Amount], [2ndAddress], [!TheReason], [TheReason!], [From] FROM SomeTable
0
Comment
Question by:Mike Eghtebas
  • 5
  • 3
  • 2
10 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 40596998
The documentation defines what a regular identifier is:

https://msdn.microsoft.com/en-us/library/ms175874.aspx

It doesn't use the word irregular, but the opposite of regular is irregular, so ....  I guess it fits.

Delimited identifiers in SQL Server use square brackets.  So the second version is correct.

(FYI, Oracle uses double quotes to cater for non standard identifier names, SQL Server uses [ ])
0
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
ID: 40597014
[ ] is T-SQL specific and like Oracle (and any other databases I would guess) because it follows standard SQL also use " ".


Thanks,

Mike
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40597024
Ah, you're right, I didn't read the delimiters part of the documentation completely :)   I've just always used [ ] when needed, had never seen " " used in T-SQL.

You learn something new everyday.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40597033
With emphases on the bold portions, do you think the query will run okay?

SELECT "Order Amount", "2ndAddress", "!TheReason", "TheReason!", "From" FROM SomeTable;
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40597156
When in doubt, test it out!

Create table SomeTable ("Order Amount" char(2), "2ndAddress" char(2), "!TheReason" char(2),
                        "TheReason!" char(2), "From" char(2))
go

Command(s) completed successfully.

insert into SomeTable values ('A', 'B', 'C', 'D', 'E')
go
(1 row(s) affected)

SELECT "Order Amount", "2ndAddress", "!TheReason", "TheReason!", "From" FROM SomeTable
go

Order Amount 2ndAddress !TheReason TheReason! From
------------ ---------- ---------- ---------- ----
A            B          C          D          E 

Open in new window


In general, avoid irregular object names like the plague.   They just cause more problems than they are worth, in my opinion.  If not now, down the road when someone else has to deal with them.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40597179
I agree with you on staying away from irregular identifiers. Also, I didn't have access to SQL Server to test this. Furthermore, I had an earlier okay from you on this. I just wanted to double check it.

Thank you for the test.

Mike
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40597629
>>"I didn't have access to SQL Server to test this. "

then try http://sqlfidde.com
there is a drop down to choose dbms type; it supplies both mssql 2008 and 2012v.handy
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40597700
Is this paid service?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40597720
No. It is limited by 8000 characters of ddl including inserts. So its great for small tests but not for anything needing large amounts of data
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40597723
Thank you.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Query Missing Money orders... 6 72
C# SQL BULK INSERT CLASS 5 36
Sql query 34 22
SQL Query for Periods 3 0
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now