Select statement format Error with spaces

Question

Why do I get an error when writing my select statement like this - Note the space/tab/newline next to my table name "Personnel Number" (I know that naming convention is bad, but I had to do it, since that is the way a excel spreadsheet looks like that I have to import regularly to the table)

SELECT md.[Personnel Number]
FROM    MainDump md
    LEFT JOIN   EthicsManagement em
    on em.[Personnel Number] = md.[Personnel Number]

And I have to write it like this to not give me an error:

SELECT md.[Personnel Number
]
FROM    MainDump md
    LEFT JOIN   EthicsManagement em
    on em.[Personnel Number] = md.[Personnel Number
]

May be you have omitted a space between Personal Number and FROM clause in first query — Santhosh
– Santhosh, Commented May 6, 2013 at 6:15
It is generally a nightmare to use spaces in your column names. I suggest sending the Excel CSV through a server-side script that maps the column names into something less prone to these errors. — Jordan Arsenault
– Jordan Arsenault, Commented May 6, 2013 at 7:41

Álvaro González · Accepted Answer · 2013-05-06 09:21:22Z

1

It's fairly obvious that your column name contains a trailing line feed. If you're absolutely sure that it's not possible to rename columns, I'm not aware of any other way to type a line feed in a column literal than the one you use:

SELECT md.[Personnel Number
]

In any case, I have a strong feeling that there's a bug in your import code. I suspect that the column is the last one in the line, you've set \n as line separator but your file actually uses \r\n.

answered May 6, 2013 at 9:21

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kayla · Accepted Answer · 2013-05-06 12:30:38Z

1

SELECT md.`Personnel Number`
FROM    MainDump md
    LEFT JOIN   EthicsManagement em
    on em.`Personnel Number` = md.`Personnel Number`

try to use [`] key behind Esc on top left in the keyboard

edited May 6, 2013 at 12:30

answered May 6, 2013 at 6:40

kayla

1561 silver badge12 bronze badges

Comments

Ruan · Accepted Answer · 2013-05-06 08:14:27Z

0

I see that if you have column names with spaces in, after you set one of those columns as a Primary key, this happens.

It adds this to your column name [Column Name]]

Note the double bracket

So Main point. To avoid having random errors like this, Don't use spaces in your columns names.

answered May 6, 2013 at 8:14

Ruan

4,32411 gold badges68 silver badges92 bronze badges

Collectives™ on Stack Overflow

Select statement format Error with spaces

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related