Spaces in column names: What a travesty. They may be legal, but they just cause trouble. To paraphrase my colleague Andy Kern, "Every time a DBA puts a space in a column name, God kills a kitten."
Virtually every incarnation of SQL has syntax for dealing with column names that contain spaces. Some want the column name in single quotes: 'My Column'
Some want it surrounded by square brackets: [My Column]
To make things more fun, some require the column reference to be preceded by the table name: MyTable.[My Column]
Now suppose you're trying to use .NET to access and manipulate data in Paradox tables, and spaces appear in some of the field names. What syntax do you use to reference such columns in your SQL?
I'll start by saying that it may depend on the ODBC driver you've chosen, but that quickly becomes a non-issue when you realize that the Intersolv Paradox ODBC drivers are the most reliable at playing nicely with .NET. Don't even start without one.
The column delimiter you want isn't a quote, a single-quote (apostrophe), or brackets. It's an arcane little bugger called the "back-tick" or, more properly, the grave. On most keyboards it appears on the same key as the tilde character, (~). It's like a backwards apostrophe.
So a simple SQL statement might look like this:
SELECT `My Field' FROM MyTable
You can verify this by using the query builder in Visual Studio.
I didn't find it there for a dumb reason: Early in my first .NET project involving Paradox tables, I'd tried the various Paradox ODBC drivers from Microsoft. With those, the query builder didn't work - so I'd given up on it - and went Googling for the proper SQL syntax. My searches came back with many hits, none of which disclosed the back-tick. Grrrrr.
So there you have it: If you need to write SQL that references columns with embedded spaces, if you're working in .NET, and if you're using the Intersolv Paradox ODBC driver, the back-tick (grave) is what you want.
Have a question? Need our services? Contact us now.
Print This