Home / Questions / 343491
Accepted
Yoel Rodriguez
Asked: 2020-04-05 17:32:19 +0800 CST 2020-04-05 17:32:19 +0800 CST

sql table with space in name and fields

  • 772

I have the following situation, I was asked to make a project with an SQL database already created but I find that the names of the tables and the fields of the tables are written with spaces and when I try to execute my query it sends me a syntax error . This database is working with an application made in Visual Basic and I am making a web application with php. Here is my code:

Select Customer.AR Cust Password, Customer.AR Cust User Name FROM AR Customer AS Customer

Mistake:

19:04:05 Kernel error: [FreeTDS][MSSQL Server] , Server EC2AMAZ-977S99A\APPBSC, Line 1 Incorrect syntax near 'Password'.

sql-server

3 Answers

  1. Best Answer

    In SQL Server the delimiters for identifiers that can be used are:

    • Double quotation marks
    • Brackets

    delimiters

    Drop table if exists "mi tabla";
Create table "mi tabla" (id int);
go
Drop table if exists [mi tabla];
Create table [mi tabla] (id int);
go

    Although both are equally valid, double quotes can sometimes be invalid.

    SET QUOTED_IDENTIFIER OFF
Drop table if exists "mi tabla";
Create table "mi tabla" (id int);

    Mens. 102, Level 15, State 1, Line 8 Incorrect syntax near 'my table'.

    GO
-- Opción válida
SET QUOTED_IDENTIFIER OFF
Drop table if exists [mi tabla];
Create table [mi tabla] (id int);
GO

    The commands have been completed successfully.

    Set Quoted_Identifier

    • 3

  2. the sql interpreter does not distinguish between fields and columns and that it has spaces.. when you call these it must go over square brackets

    select [Id],[Doc Number],[Internal Doc Number] 
from [TABLEA Con Espacio];

    if you call them like this:

    select Id,Doc Number,Internal Doc Number from TABLEA Con Espacio

    if you need to rename the column to parse it into an object you can do it like this

    select [Id],[Doc Number] as Doc_Number
from [TABLEA Con Espacio];

    the engine does not know how far the columns and tables are

    Here you can see an example

    in the last line there is a query with an error (without brackets)

    I hope the information is useful to you

    • 1

  3. just use backticks `nombre de tabla` Alt Gr+ { or } to get them

    • 0