Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Selecting a column with period in the column name SQL Server

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]

where SERVER.pid_astatus[07][0].F_CV.Value is the name of the column

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]`

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

EDIT:

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] 

However it does not work inside Incorrect syntax near the keyword 'from'.

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

EDIT

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

I had to escape the column escape :)

like image 221
efbenson Avatar asked Dec 28 '22 10:12

efbenson


2 Answers

You only need to escape these ]

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

This works for me

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

SELECT [pid_astatus[07]][0]].F_CV.Value]
FROM #t
like image 182
Martin Smith Avatar answered Mar 05 '23 19:03

Martin Smith


(Edited to reflect new knowledge, if you like this vote for Martin Smith's answer instead!)

Escape the ] by doubling them:

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

Based on your comment, try:

SELECT [SERVER.pid_astatus[07]][0]].F_CV.Value] FROM iHist...ihTrend
like image 31
Andomar Avatar answered Mar 05 '23 19:03

Andomar