Reason why oracle is case sensitive?

by

By default, Oracle identifiers (table names, column names, etc.) are case-insensitive. You can make them case-sensitive by using quotes around them (eg: SELECT * FROM "My_Table" WHERE "my_field" = 1). SQL keywords (SELECT, WHERE, JOIN, etc.) are always case-insensitive.

On the other hand, string comparisons are case-sensitive (eg: WHERE field='STRING' will only match columns where it’s 'STRING') by default. You can make them case-insensitive by setting NLS_COMP and NLS_SORT to the appropriate values (eg: LINGUISTIC and BINARY_CI, respectively).

Note: When inquiring data dictionary views (eg: dba_tables) the names will be in upper-case if you created them without quotes, and the string comparison rules as explained in the second paragraph will apply here.

Some databases (Oracle, IBM DB2, PostgreSQL, etc.) will perform case-sensitive string comparisons by default, others case-insensitive (SQL Server, MySQL, SQLite). This isn’t standard by any means, so just be aware of what your db settings are.

More Related Contents:

  1. Calculate difference between 2 date / times in Oracle SQL
  2. Can you SELECT everything, but 1 or 2 fields, without writer’s cramp?
  3. How to generate entire DDL of an Oracle schema (scriptable)?
  4. How to handle Day Light Saving in Oracle database
  5. How to get records randomly from the oracle database?
  6. Oracle PL/SQL – Raise User-Defined Exception With Custom SQLERRM
  7. Auto Increment for Oracle
  8. Get resultset from oracle stored procedure
  9. Deploying and Configuring ODP.NET to work without installation with Entity Framework
  10. Add a auto increment primary key to existing table in oracle [duplicate]
  11. ways to avoid global temp tables in oracle
  12. Spring Batch ORA-08177: can’t serialize access for this transaction when running single job, SERIALIZED isolation level
  13. How to generate an entity-relationship (ER) diagram using Oracle SQL Developer
  14. Number of rows affected by an UPDATE in PL/SQL
  15. How to use a table type in a SELECT FROM statement?
  16. Dynamically pivoting a table Oracle
  17. Spool Command: Do not output SQL statement to file
  18. URL string format for connecting to Oracle database with JDBC
  19. Is there any way to have the JBoss connection pool reconnect to Oracle when connections go bad?
  20. Get ID of last inserted record in oracle db
  21. oracle SQL plus how to end command in SQL file?
  22. Why is the GETDATE() an invalid identifier
  23. Get the number of days between two dates in Oracle, inclusive of the dates
  24. ORA-04091: table [blah] is mutating, trigger/function may not see it
  25. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  26. How to use parameters in a ‘where value in…’ clause?
  27. How to call an Oracle function with a Ref Cursor as Out-parameter from C#?
  28. How to Register Pluggable Database(PDB) with new created LISTENER
  29. Grant Select on all Tables Owned By Specific User
  30. ORA-12704: character set mismatch

Leave a Comment