mysql case sensitive query for fieldnames

Question

I would like the query to select only if there is a column named firstName. If the column is firstname then the query should not return anything.

SELECT firstName as currentName from tableNew WHERE id = $Id

I tried the code below but does not work

SELECT binary firstName as currentName from tableNew WHERE id = $Id

Can you please help?

Not finding the right column would end by a hard error returned from the server (such query is invalid). The schema is usually not to be subjected to such conditions and columns and other identifiers are case insensitive by design in MySQL ([manual](https://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html)). What problem are you trying to solve by this? — jkavalik, Sep 25 '15 at 10:19
You should test if the column exists. This link my help http://stackoverflow.com/a/5943905/634698 — Thallius, Sep 25 '15 at 10:25
I am not sure if you can fetch the records based on column existence. But you can check out the presence of column by it's name. Hope you found this solution relevant to you problem. Check this - http://stackoverflow.com/questions/3395798/mysql-check-if-a-column-exists-in-a-table-with-sql — Ranjana, Sep 25 '15 at 10:30
@jkavalik I am trying to flag an error when the user enters an incorrect field name. — Santosh Pillai, Sep 25 '15 at 10:48
@SantoshPillai then either list of "allowed" values in your app or the `information_schema` solution. — jkavalik, Sep 25 '15 at 11:03

Abhishek Sharma · Answer 1 · 2015-09-25T10:31:36.917

1

On Unix, table names are case sensitive. On Windows, they are not.

Column, index, and stored routine names are not case sensitive on any platform, nor are column aliases.

edited Sep 25 '15 at 10:31

answered Sep 25 '15 at 10:24

Abhishek Sharma

6,689
1
14
20

score 1 · Answer 2 · answered Sep 25 '15 at 10:33

If you have access to INFORMATION_SCHEMA do this:

SELECT firstName as currentName from tableNew WHERE id = $Id AND (SELECT COUNT(TABLE_NAME) 
FROM information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'mydbname' 
AND TABLE_NAME = 'tableNew' 
AND BINARY COLUMN_NAME = 'firstName') > 0;

mysql case sensitive query for fieldnames

2 Answers2