27

Suppose I want to use code like this:

select 'Response Status Code', 'Client Response Status Code' 
from TC_Sessions (NOLOCK)
WHERE StartDate BETWEEN '05-15-2012' AND '06-01-2012'
AND SupplyID = 3367

How do you do this in SQL Server?

thank you!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Caffeinated
  • 11,982
  • 40
  • 122
  • 216

5 Answers5

46
select [Response Status Code], [Client Response Status Code]
from TC_Sessions (NOLOCK) 
WHERE StartDate BETWEEN '05-15-2012' AND '06-01-2012' 
AND SupplyID = 3367

Wrap the names in square brackets.

It is , however, best to avoid spaces in names if possible. It just creates more work for you down the road...

Jimbo
  • 2,529
  • 19
  • 22
  • 1
    I confirm, avoid space or special characters in all you names. Never forget when you need to translate these name in a language (C#, Java, ...) the variable name do not accept spaces and you can then get conflicts. More infor about this https://stackoverflow.com/q/55295594/196526 – Bastien Vandamme Mar 23 '19 at 02:28
6

I understand the need for creating columns with spaces in them, especially for reports that users will interact with, it's not very user-friendly to have a column heading labeled as 'Client_Response_Status_Code'.

Certainly the previous answers were all technically correct, however I would suggest rather than create columns with spaces in their names, create views and change the column names there instead...

For Example:

create view TC_Sessions_Report as
select response_status_code AS [Response State Code], 
       client_response_status_code as [Client Response Status Code]

...

This way you don't have to deal with those "friendly" column names everywhere in your code, but where you do need to use the friendly column names you can use the view instead...

Jack
  • 10,943
  • 13
  • 50
  • 65
Don L
  • 61
  • 1
  • 1
  • I confirm this is the good way to do. Table are for cade and views are for reports. this is, for me, good practice. I confirm, avoid space or special characters in all you names. Never forget when you need to translate these name in a language (C#, Java, ...) the variable name do not accept spaces and you can then get conflicts. More infor about this stackoverflow.com/q/55295594/196526 – Bastien Vandamme 1 min ago – Bastien Vandamme Mar 23 '19 at 02:31
5
select
   [Response Status Code],
   [Client Response Status Code] 
from TC_Sessions (NOLOCK)
WHERE StartDate BETWEEN '05-15-2012' AND '06-01-2012'
AND SupplyID = 3367

This will work for table names as well, and is also a good practice if your field name is a reserved word or similar, such as [Order] or [Month]

A better practice is to avoid those entirely...

Jamie F
  • 23,189
  • 5
  • 61
  • 77
1

Generally in sql you dont do it. you do response_status_code , if you have to use spaces i would try escaping them with \ or using [Response Status Code] or something

Markus Mikkolainen
  • 3,397
  • 18
  • 21
1

You can have columns with spaces, but it is usually avoided. You need to wrap your column in brackets to select it:

select [Response Status Code], [Client Response Status Code]
Limey
  • 2,642
  • 6
  • 37
  • 62