0

When is comparing strings case-sensitive and when case-insensitive in popular databases such as PostgreSQL, MySQL, MariaDB, Oracle, SQL Server, and SQLite?

I mean comparison using operators like: 'ab' = 'AB', or comparison of strings performed inside string functions like: POSITION('b' IN 'ABC'), INSTR('ABC', 'b'), REPLACE('ABC', 'b', 'x'), TRANSLATE('ABC', 'b', 'x'), TRIM('XabcX', 'x').

I think that I can know the answer but I don't know if it is correct.

Additionally, does the SQL standard define the case-sensitivity of string comparison?

Unfortunately, I only found a question about case-sensitivity of SQL syntax, and not in string comparison.

Edit: I am asking about default setting of the RDBMS, without additional setting the collation of a database, table, nor column.

I am asking only about ASCII letters A-Z and a-z.

iwis
  • 1,382
  • 1
  • 13
  • 26
  • SQL is always case sensitive – drum Nov 19 '20 at 21:05
  • 1
    For SQL Server it depends on collation. Most people end up with case insensitive, accent sensitive IME – Martin Smith Nov 19 '20 at 21:06
  • 1
    I depends on the database, and on various factors, such as the collation of the database, of the table, and of the column. Your question as it stands is quite broad and cannot be accurately answered without you providing more details. – GMB Nov 19 '20 at 21:07
  • @GMB: I added the information that I am interested in the default settings of the RDBMS, without setting the collation of the database, table, nor column. Is it always case-sensitive in this situation, by default? – iwis Nov 19 '20 at 21:21
  • @GMB: I am also interested only in ASCII letters A-Z and a-z, and only in PostgreSQL, MySQL, MariaDB, Oracle, SQL Server, and SQLite databases. Please let me know if there there are any other problems with the question. – iwis Nov 19 '20 at 21:33
  • For SQL Server on premise you have to define a default server collation when installing it. This will be used if you don't specify anything different at other levels. The default option in the setup UI varies depending on OS but is CI_AS in the two cases I know about – Martin Smith Nov 20 '20 at 08:32

1 Answers1

0

Oracle DB functions/operators are case sensitive, with some exceptions including regexp_like method that can be switched to case-sensitive mode ;

--case sensitive demo
select q.txt 
from (select 'myPhrase' txt from dual) q
where regexp_like(q.txt, 'myphrase');  --empty result set

select q.txt 
from (select 'myPhrase' txt from dual) q
where q.txt like 'myphrase'; --empty result set

select q.txt 
from (select 'myPhrase' txt from dual) q
where q.txt like 'myPhrase'; --exact match; returns value

select q.txt 
from (select 'myPhrase' txt from dual) q
where regexp_like(q.txt, 'myphrase', 'i'); --case insensitive switch 'i'; returns value

select q.txt 
from (select 'myPhrase' txt from dual) q
where lower(q.txt) like 'myphrase'; --enforced match; returns value;

Here's reference to docs for Collation rules for different SQL Operations and for Data Type Comparison Rules

Once a pseudo-collation is determined as the collation to use, NLS_SORT and NLS_COMP session parameters are checked to provide the actual named collation to apply Usually these two are set to BINARY

SELECT
    t.parameter,
    t.value
FROM
    nls_database_parameters t
WHERE
    t.parameter IN (
        'NLS_COMP', 'NLS_SORT'
    );
Elzzz
  • 131
  • 5