Is SQL syntax case sensitive?
The SQL Keywords are case-insensitive (SELECT
, FROM
, WHERE
, etc), but are often written in all caps. However in some setups table and column names are case-sensitive. MySQL has a configuration option to enable/disable it. Usually case-sensitive table and column names are the default on Linux MySQL and case-insensitive used to be the default on Windows, but now the installer asked about this during setup. For MSSQL it is a function of the database's collation setting.
Here is the MySQL page about name case-sensitivity
Here is the article in MSDN about collations for MSSQL
How to do a case sensitive search in WHERE clause (I'm using SQL Server)?
Can be done via changing the Collation. By default it is case insensitive.
Excerpt from the link:
SELECT 1
FROM dbo.Customers
WHERE CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS
AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS
Or, change the columns to be case sensitive.
How can I write a case sensitive WHERE CLAUSE in SQL Server?
Normally SQL Server is not case sensitive. So 'ABC'='abc'
is true in a where clause
.
To make a where
clause case sensitive, you can use COLLATE
. Please try the below query instead:
WHERE
[Address] = UPPER([Address]) COLLATE SQL_Latin1_General_CP1_CS_AS
OR
[Address] = LOWER([Address]) COLLATE SQL_Latin1_General_CP1_CS_AS
SQL- Ignore case while searching for a string
Use something like this -
SELECT DISTINCT COL_NAME FROM myTable WHERE UPPER(COL_NAME) LIKE UPPER('%PriceOrder%')
or
SELECT DISTINCT COL_NAME FROM myTable WHERE LOWER(COL_NAME) LIKE LOWER('%PriceOrder%')
Is the LIKE operator case-sensitive with SQL Server?
It is not the operator that is case sensitive, it is the column itself.
When a SQL Server installation is performed a default collation is chosen to the instance. Unless explicitly mentioned otherwise (check the collate clause bellow) when a new database is created it inherits the collation from the instance and when a new column is created it inherits the collation from the database it belongs.
A collation like sql_latin1_general_cp1_ci_as
dictates how the content of the column should be treated. CI stands for case insensitive and AS stands for accent sensitive.
A complete list of collations is available at https://msdn.microsoft.com/en-us/library/ms144250(v=sql.105).aspx
(a) To check a instance collation
select serverproperty('collation')
(b) To check a database collation
select databasepropertyex('databasename', 'collation') sqlcollation
(c) To create a database using a different collation
create database exampledatabase
collate sql_latin1_general_cp1_cs_as
(d) To create a column using a different collation
create table exampletable (
examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null
)
(e) To modify a column collation
alter table exampletable
alter column examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null
It is possible to change a instance and database collations but it does not affect previously created objects.
It is also possible to change a column collation on the fly for string comparison, but this is highly unrecommended in a production environment because it is extremely costly.
select
column1 collate sql_latin1_general_cp1_ci_as as column1
from table1
Is database record is case sensitive?
By default it is not case sensitive. So it will treat 'ABC' and 'abc' as same. But to make it case sensitive you have to use COLLATE.
You can find the detailed explanation with example HERE . This applys to join also.
SQL Server check case-sensitivity?
Collation can be set at various levels:
- Server
- Database
- Column
So you could have a Case Sensitive Column in a Case Insensitive database. I have not yet come across a situation where a business case could be made for case sensitivity of a single column of data, but I suppose there could be.
Check Server Collation
SELECT SERVERPROPERTY('COLLATION')
Check Database Collation
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;
Check Column Collation
select table_name, column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @table_name
Reason why oracle is case sensitive?
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.
Related Topics
SQL Server, Division Returns Zero
In Sql, How to "Group By" in Ranges
Delete Duplicate Rows from Small Table
How to Combine Date from One Field With Time from Another Field - Ms SQL Server
SQL Select Where Field Contains Words
Grouped Limit in Postgresql: Show the First N Rows For Each Group
Selecting Count(*) With Distinct
Group by Minimum Value in One Field While Selecting Distinct Rows
Function Vs. Stored Procedure in SQL Server
In MySQL Queries, Why Use Join Instead of Where
SQL Best Practice to Deal With Default Sort Order
How to Handle a Single Quote in Oracle Sql
In VS Any Operator in Postgresql
MySQL Results as Comma Separated List
Is There Any Function in Oracle Similar to Group_Concat in MySQL
Delete from Two Tables in One Query