[Back to DELPHI SWAG index] [Back to Main SWAG index] [Original]

    Implementing SQL with spaces or special characters in field/column names

    Implementing SQL statements in Delphi's TQuery component (or the
    SQL query facilities of Database Desktop, Visual dBASE or Paradox
    for Windows) requires special syntax for any columns that contain
    spaces or special characters.

    Using the Biolife.DB table of from Delphi's demo data to
    illustrate, and without the use of any special syntax
    requirements, a SQL Select statement might be formed as follows,

    SELECT
     Species No,
     Category,
     Common_Name,
     Species Name,
     Length (cm),
     Length_In,
     Notes,
     Graphic
    FROM
     BIOLIFE

    While appearing normal, the space in the species number and name
    columns and the column expressing length in centimeters - as well
    as the parentheses present - cause syntax errors.

    Two changes must be taken to correct the syntax of the above SQL
    statement.  First, any columns containing spaces or special
    characters must be surrounded by single (apostrophe) or double
    quotes.  Secondly, a table reference and a period must precede
    the quoted column name.  This second requirement is particularly
    important since a quoted string alone is interpreted as a string
    expression to be yielded as a column value.  A properly formatted
    statement follows:

    SELECT
     BIOLIFE."Species No",
     BIOLIFE."Category",
     BIOLIFE."Common_Name",
     BIOLIFE."Species Name",
     BIOLIFE."Length (cm)",
     BIOLIFE."Length_In",
     BIOLIFE."Notes",
     BIOLIFE."Graphic"
    FROM
     "BIOLIFE.DB" BIOLIFE

    The above example uses the table alias BIOLIFE as the table
    reference that precedes the column name.  This reference may take
    the form of an alias name, the actual table name, or a quoted
    file name when using dBASE or Paradox tables.  The following
    SQL statements would serve equally well.

    Note: This SQL statement may be used provided that the necessary
    alias is already opened.  In the case of the TQuery this means the
    alias is specified in the DatabaseName property.

    SELECT
     BIOLIFE."Species No",
     BIOLIFE.Category,
     BIOLIFE.Common_Name,
     BIOLIFE."Species Name",
     BIOLIFE."Length (cm)",
     BIOLIFE.Length_In,
     BIOLIFE.Notes,
     BIOLIFE.Graphic
    FROM
     BIOLIFE

    If an alias is not available then the entire path to the table
    can be specified as in this example:

    SELECT
     "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Species No",
     "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Category",
     "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Common_Name",
     "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Species Name",
     "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Length (cm)",
     "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Length_In",
     "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Notes",
     "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Graphic"
    FROM
     "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"

    Finally, two facilities that automatically handle this special
    formatting exist.  The first is the Visual Query Builder that is
    a part of the Client/Server version of Delphi.  The Visual Query
    Builder performs this formatting automatically as the query is built.
    The other facility is Database Desktop's Show SQL feature, available
    when creating or modifying a QBE-type query.  After selecting
    Query|Show SQL from the main menu, the displayed SQL text may be
    cut and pasted where needed.

[Back to DELPHI SWAG index] [Back to Main SWAG index] [Original]