tokenizing column name starts with a number

[cptb]

Hello, Thanks for providing such a great solution to parse SQL scripts.
I am facing a table contains a few columns that their names start with a numeric number.
For example:
SELECT 1st_surface, 2nd_surface FROM dbo.RoadLayer;

After I inspected the tokens, I found that the letter 1 and 2 were tokenized as numeric values and got separated from the real column names. Here is my example output:

Token type: Keyword	Text: select
Token type: NumericLiteral	Text: 1
Token type: Identifier	Text: st_chip_size
Token type: Character	Text: ,
Token type: NumericLiteral	Text: 2
Token type: Identifier	Text: nd_chip_size
Token type: Keyword	Text: from
Token type: Identifier	Text: RoadLayer

---

Is that possible to keep the column name as a whole identifier in such case? Could you please point me to the code that actually does the column name tokenizing?

Thank you for your help in advance.

Paul

[bruce-dunwiddie]

I'm currently surprised by the issue.

Simple answer to your question is that if this is indeed valid TSQL syntax, then I need to fix my parser logic to yes return the whole thing as an identifier and not break it out into a number and an identifier, but I will need to verify the validity of the syntax, and the results you're getting.

The code you're asking about is in https://github.com/bruce-dunwiddie/tsql-parser/blob/master/TSQL_Parser/TSQL_Parser/TSQLTokenizer.cs , but that's real base level code that the entire parser library is built off of, and although it's fairly straightforward and almost commented, it's a little verbose. Any changes there would need to be heavily tested.

[bruce-dunwiddie]

Ok, I think this is a nonissue, and I'll explain why.

This is not valid TSQL:

CREATE TABLE dbo.RoadLayer
(
	1st_surface VARCHAR(MAX),
	2nd_surface VARCHAR(MAX)
);

Incorrect syntax near '1'.

To create the table structure that you're describing, you have to use escape syntax:

CREATE TABLE dbo.RoadLayer
(
	[1st_surface] VARCHAR(MAX),
	[2nd_surface] VARCHAR(MAX)
);

But even once the table is created, while your example TSQL statement passes syntax checks, meaning that it's technically valid TSQL, it does not return the result that you're describing.

INSERT INTO dbo.RoadLayer([1st_surface], [2nd_surface]) VALUES ('first', 'second');

SELECT 1st_surface, 2nd_surface FROM dbo.RoadLayer;

Which shows that the TSQL is logically being executed the equivalent of:

SELECT 1 AS st_surface, 2 AS nd_surface FROM dbo.RoadLayer;

And this is the way that the parser is also currently parsing the statement, splitting out the value and then the identifier as an alias.

[cptb]

Great answer and explanation! Thank you very much for clarifying the cause. I was a bit confused about the SQL Server parsed this select query successfully and not complaining about missing square bracket though.