在列名称中选择带句点的列SQL Server [英] Selecting a column with period in the column name SQL Server

问题描述

我与Proficy Historian链接，该列允许在列名称中使用句点.因为数据是以非DBMS格式存储的，所以我不能使用openquery来获取数据，因为表没有设置模式.因此，我必须使用四部分名称语法来获取数据.此示例有效:

I am linked to a Proficy Historian that allows periods in the column names. Because the data is stored in a non DBMS format I can not use openquery to get the data because there is no set schema to the tables. So I must use four part name syntax to get the data. This example works:

SELECT * FROM iHist...[SELECT * FROM ihTrend]

但这会失败，并且'.'附近的语法不正确.

but this fails with Incorrect syntax near '.'.

SELECT * FROM iHist...[SELECT [SERVER.pid_astatus[07][0].F_CV.Value] FROM ihTrend]

其中SERVER.pid_astatus[07][0].F_CV.Value是列的名称

这同样会因关键字'from'附近的语法错误而失败.

This fails as well with Incorrect syntax near the keyword 'from'.

SELECT * FROM 
    iHist...[SELECT [SERVER.pid_astatus[[07]][[0]].F_CV.Value] from ihTrend]`

关于如何使SQL Server将此列视为列的任何想法?

Any ideas on how I can make SQL Server see this as a column?

Martins建议使用右方括号来使方括号转义仅在sql调用的外部起作用

Martins suggestion of the right brackets to escape the brackets work only on the outside of the sql call

SELECT [SERVER.pid_astatus[07]][0]].F_CV.Value] FROM iHist...[SELECT * FROM ihTrend] 

但是它在关键字'from'附近的语法不正确.

SELECT * FROM iHist...[SELECT [SERVER.pid_astatus[07]][0]].F_CV.Value] FROM ihTrend]

编辑

SELECT * FROM iHist...[SELECT [SERVER.pid_astatus[07]][0]].F_CV.Value]] FROM ihTrend] 

我不得不对列进行转义:)

I had to escape the column escape :)

推荐答案

您只需要转义这些]

[pid_astatus[07]][0]].F_CV.Value]

这对我有用

CREATE TABLE #t(
    [pid_astatus[07]][0]].F_CV.Value] int
) 

SELECT [pid_astatus[07]][0]].F_CV.Value]
FROM #t

这篇关于在列名称中选择带句点的列SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！