Table or Column Name Cannot Start with Numeric

Table or column name cannot start with numeric?

It comes from the original sql standards, which through several layers of indirection eventually get to an identifier start block, which is one of several things, but primarily it is "a simple latin letter". There are other things too that can be used, but if you want to see all the details, go to http://en.wikipedia.org/wiki/SQL-92 and follow the links to the actual standard ( page 85 )

Having non numeric identifier introducers makes writing a parser to decode sql for execution easier and quicker, but a quoted form is fine too.

---

Edit: Why is it easier for the parser?

The problem for a parser is more in the SELECT-list clause than the FROM clause. The select-list is the list of expressions that are selected from the tables, and this is very flexible, allowing simple column names and numeric expressions. Consider the following:

SELECT 2e2 + 3.4 FROM ...

If table names, and column names could start with numerics, is 2e2 a column name or a valid number (e format is typically permitted in numeric literals) and is 3.4 the table "3" and column "4" or is it the numeric value 3.4 ?

Having the rule that identifiers start with simple latin letters (and some other specific things) means that a parser that sees 2e2 can quickly discern this will be a numeric expression, same deal with 3.4

While it would be possible to devise a scheme to allow numeric leading characters, this might lead to even more obscure rules (opinion), so this rule is a nice solution. If you allowed digits first, then it would always need quoting, which is arguably not as 'clean'.

---

Disclaimer, I've simplified the above slightly, ignoring corelation names to keep it short. I'm not totally familiar with postgres, but have double checked the above answer against Oracle RDB documentation and sql spec

Column Name beginning with a number?

If you are using column names that start with a number then you need to use double quotes. For example:

create table foo (
"3RD_DIAG_CODE" varchar2(10 byte) --make sure you use uppercase for variable name
);

insert into foo values ('abc');
insert into foo values ('def');
insert into foo values ('ghi');
insert into foo values ('jkl');
insert into foo values ('mno');
commit;

select * from foo;

3RD_DIAG_C
----------
abc
def
ghi
jkl
mno

select 3RD_DIAG_CODE from foo;

RD_DIAG_CODE
------------
       3
       3
       3
       3
       3

select "3RD_DIAG_CODE" from foo;

3RD_DIAG_C
----------
abc
def
ghi
jkl
mno

Edit: As for the error message itself, you are probably (as BQ wrote) missing a comma from the select clause.

Datatable column name omits names starting with a number

From the SQL standards, table names cannot start with a number.
See this post for further information: Table or column name cannot start with numeric?

How to handle numbers in column names in Snowflake

The comments where right. Adding double quotes to the columns containing numbers.

Can a number be used to name a MySQL table column?

From the docs:

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

Which means you must quote it with back ticks like `25`:

UPDATE table SET `25`='100' WHERE id='1'

Cannot display data from table where table column name starts with a number codeigniter

Yes, you can use the following

<?php echo $cus->{'3rdfloorslab'}; ?>

Here is some documentation on variable parsing. Though it's not the best practice.

If you're getting the data from a database, you could do something like

SELECT 3rdfloorslab AS thirdfloorslab

Which would help keep your code to the same standard.

CASE WHEN Statement error in SQL: Column name or number of supplied values does not match table definition

I think you mean to be doing an update, not an insert at the end. It looks like you're trying to populate the Segment_Length field based on the Segment Miles, not add new rows with only the SegmentID and Segment_Length. If so - something like:

UPDATE Classifications
   SET Segment_Length = CASE 
            WHEN Segment_Miles >= 0 and Segment_Miles <= 50 THEN 'Short'
            WHEN Segment_Miles > 50 and Segment_Miles <= 100 THEN 'Long'
            ELSE '?'
        END

The reason you are getting the error is you are trying to insert records into a table with 7 fields, but your select statement contains only 2 fields. If you are actually trying to insert new records, then you need to change the INSERT line to specify which two fields you are populating:

INSERT INTO Classifications (SegmentID, Segment_Length) 
...

---

Related Topics